Postgresql – Export and import a PostgreSQL database with a different name

databasedumppostgresqlrestore

Is there a way to export a PostgreSQL database and later import it with another name?

I'm using PostgreSQL with Rails and I often export the data from production, where the database is called blah_production and import it on development or staging with names blah_development and blah_staging. On MySQL this is trivial as the export doesn't have the database anywhere (except a comment maybe), but on PostgreSQL it seems to be impossible. Is it impossible?

I'm currently dumping the database this way:

pg_dump blah > blah.dump

I'm not using the -c or -C options. That dump contains statements such as:

COMMENT ON DATABASE blah IS 'blah';

ALTER TABLE public.checks OWNER TO blah;

ALTER TABLE public.users OWNER TO blah;

When I try to import with

psql blah_devel < blah.dump

I get

WARNING:  database "blah" does not exist

ERROR:  role "blah" does not exist

Maybe the problem is not really the database but the role?

If I dump it this way:

pg_dump --format=c blah > blah.dump

and try to import it this way:

pg_restore -d blah_devel < tmp/blah.psql

I get these errors:

pg_restore: WARNING:  database "blah" does not exist
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1513; 1259 16435 TABLE checks blah
pg_restore: [archiver (db)] could not execute query: ERROR:  role "blah" does not exist
    Command was: ALTER TABLE public.checks OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1509; 1259 16409 TABLE users blah
pg_restore: [archiver (db)] could not execute query: ERROR:  role "blah" does not exist
    Command was: ALTER TABLE public.users OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1508; 1259 16407 SEQUENCE users_id_seq blah
pg_restore: [archiver (db)] could not execute query: ERROR:  role "blah" does not exist
    Command was: ALTER TABLE public.users_id_seq OWNER TO blah;
pg_restore: [archiver (db)] Error from TOC entry 1824; 0 0 ACL public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  role "postgres" does not exist
    Command was: REVOKE ALL ON SCHEMA public FROM postgres;
pg_restore: [archiver (db)] could not execute query: ERROR:  role "postgres" does not exist
    Command was: GRANT ALL ON SCHEMA public TO postgres;
WARNING: errors ignored on restore: 11

Any ideas?

I've seen out there some people using sed scripts to modify the dump. I'd like to avoid that solution but if there are no alternative I'll take it. Has anybody wrote a script to alter the dump's database name ensure no data is ever altered?

Best Answer

The solution was dumping it like this:

pg_dump --no-owner --no-acl blah > blah.psql

and importing it like this:

psql blah_devel < blah.psql > /dev/null

I still get this warning:

WARNING:  database "blah" does not exist

but the rest seems to work.