CodingPipe.com

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

Additional References #