Bojan Veljanovski's Tech Blog

PostgreSQL Snippets

Snippets

Set the PostgreSQL environment variables in your PowerShell session, so you won't need to add them to every single CLI command in that same session:

# Export PostgreSQL variables
$env:PGHOST = "localhost"
$env:PGPORT = 5432
$env:PGUSER = "postgres"
$env:PGPASSWORD = "postgres"

# So now, you won't need to add this part in the commands:
# --host localhost --port 5432 --username "postgres"

List all databases

psql --command \l

Create backup (database dump):

pg_dump --format custom --no-owner --no-privileges --dbname mydb --file "C:/backups/mydb.backup"

Restore backup:

pg_restore --format custom --no-owner --no-privileges --single-transaction --dbname mydb "C:/backups/mydb.backup"

Restore backup only in a specific schema:

pg_restore --schema "schema_name_1" --format custom --no-owner --no-privileges --single-transaction --dbname mydb "C:/backups/mydb.backup"

Create user:

psql --command "CREATE ROLE testuser NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'testpsw';"

List users:

psql --command \du

Create database:

psql --command "CREATE DATABASE mydb;"

psql --command "CREATE DATABASE mydb OWNER testuser ENCODING 'UTF8';"

Create schema in database with authorization to another user

psql --command "CREATE SCHEMA IF NOT EXISTS schema3 AUTHORIZATION testuser2;" mydb

List all schemas in database:

psql --command \dn mydb

Iterate over all databases in PowerShell:

$databases = psql --tuples-only --no-align --command "SELECT datname FROM pg_database WHERE datistemplate = false"

# Iterate over all databases
foreach ($db in $databases)
{
    if ($db) {
        Write-Host "Processing database: $db"
    }
}

Additional References