When working with PostgreSQL database migrations between environments (production → development/staging), developers frequently encounter ownership and naming conflicts. Unlike MySQL where database names aren't hardcoded in dumps, PostgreSQL includes specific references that cause these errors:

ERROR:  role "blah" does not exist
WARNING:  database "blah" does not exist

PostgreSQL's dump files contain three problematic elements:

• Database name references in COMMENT statements
• Role/owner specifications in ALTER TABLE commands
• Schema-level permissions (ACL entries)

The cleanest approach is using these pg_dump options:

pg_dump --no-owner --no-acl --format=custom -f blah.dump blah_production

Then restore with:

pg_restore --no-owner --no-acl -d blah_development blah.dump

Key flags explanation:
• --no-owner: Skips OWNER TO statements
• --no-acl: Omits privilege assignments
• --format=custom: Enables selective restore

For plain SQL dumps, you can combine sed with pg_dump:

pg_dump blah_production | \
sed -e 's/blah_production/blah_development/g' \
    -e 's/OWNER TO blah/OWNER TO current_user/g' \
    -e 's/COMMENT ON DATABASE blah/-- COMMENT ON DATABASE blah/g' \
    > modified_dump.sql

When you need to preserve certain ownership structures, create the roles first:

-- Before restore
CREATE ROLE blah_devel WITH LOGIN;
ALTER DATABASE blah_development OWNER TO blah_devel;

-- Then restore with:
pg_restore --role=blah_devel -d blah_development blah.dump

For automated environments, use this bash script template:

#!/bin/bash

SRC_DB="blah_production"
TARGET_DB="blah_${ENVIRONMENT}"
DUMP_FILE="/tmp/db_dump.pgcustom"

pg_dump --no-owner --no-acl --format=custom -Fc -f $DUMP_FILE $SRC_DB

psql -c "CREATE DATABASE $TARGET_DB;"
pg_restore --no-owner --no-acl -d $TARGET_DB $DUMP_FILE

# Verify restoration
psql -d $TARGET_DB -c "SELECT count(*) FROM pg_tables WHERE schemaname = 'public';"

If you encounter:

ERROR:  must be owner of database

Solution:

psql -c "ALTER DATABASE blah_development OWNER TO current_user;"

---

When working with PostgreSQL database migrations between environments (production → development/staging), we often encounter permission and naming conflicts. Unlike MySQL where database names aren't hardcoded in dumps, PostgreSQL includes explicit references to:

COMMENT ON DATABASE original_name;
ALTER TABLE public.table_name OWNER TO original_role;

Here are three production-tested approaches:

1. Using pg_dump with --no-owner

The most straightforward solution:

pg_dump --no-owner --format=c original_db > dump.file
pg_restore -d new_db_name dump.file

This prevents ownership assignments during restore. You'll need to manually set permissions afterward with:

GRANT ALL ON SCHEMA public TO new_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO new_role;

2. Creating a Template Database

For repeated migrations:

CREATE DATABASE template_db TEMPLATE original_db;
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template_db';
CREATE DATABASE new_db_name TEMPLATE template_db;

3. Using Custom Format with pg_restore Options

For maximum control:

pg_dump -Fc original_db > dump.file
pg_restore \
  --dbname=new_db_name \
  --no-owner \
  --no-privileges \
  --role=target_role \
  dump.file

When you need to preserve specific permissions but change others:

# Dump with comments but without owners
pg_dump -Fc --no-owner --no-acl original_db > dump.file

# Restore with custom permissions
pg_restore -d new_db_name \
  --use-list=permissions.lst \
  dump.file

Create permissions.lst containing:

COMMENT ON DATABASE
GRANT ALL ON SCHEMA public TO
GRANT SELECT ON ALL TABLES IN SCHEMA public TO

For Rails environments, create a shell script:

#!/bin/bash

# Export
pg_dump -Fc --no-owner --no-acl ${PRODUCTION_DB} > /tmp/dump.pg

# Import
createdb ${TARGET_DB}
psql -d ${TARGET_DB} -c "CREATE ROLE ${TARGET_USER} LOGIN"
pg_restore -d ${TARGET_DB} --no-owner --no-acl /tmp/dump.pg
psql -d ${TARGET_DB} -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${TARGET_USER}"