PostgreSQL Query and Command 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"

Backup command

pg_dump --format custom --no-owner --no-privileges --no-acl --host localhost --port 5432 --username "postgres" --dbname mydb --file "C:/backups/mydb.backup"

Restore command

# Step 1: Create the database if it doesn't exist
psql --host localhost --port 5432 --username "postgres" --dbname postgres -c "CREATE DATABASE mydb WITH OWNER postgres ENCODING 'UTF8';"

# Step 2: Drop the public schema in the target database
psql --host localhost --port 5432 --username "postgres" --dbname mydb -c "DROP SCHEMA IF EXISTS public CASCADE;"

# Step 3: Restore the backup file
pg_restore --format custom --no-owner --no-privileges --no-acl --clean --if-exists --exit-on-error --single-transaction --host localhost --port 5432 --username "postgres" --dbname mydb "D:/backups/mydb-2024-09-01-1150.backup"

Other

List all databases

psql --command \l

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"
    }
}

PostgreSQL Query and Command Snippets

Backup command

Restore command

Other

Additional References