Home / server / Questions / 228170
Accepted
Pablo
Asked: 2011-01-29 07:31:08 +0800 CST

Export and import a PostgreSQL database with a different name?

  • 772

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?

database restore postgresql dump

3 Answers

  1. 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.

    • 45

  2. If you are creating a text dump you can export the database without the CREATE DATABASE bits (i.e. don't specify -c and -C options to pg_dump) ; This will prevent Postgres from trying to drop, create & connect to the database.

    If you're using one of the archive formats you can specify the -d option to pg_restore to name the database you want to restore to.

    Check the man pages for pg_dump and pg_restore for more details, and don't forget to mount a scratch monkey before you try this on production systems in case I left out some important detail.

    • 11

  3. Now pg_restore has -d option and you could set database name for importing data.

    on source:

    pg_dump -v -Fc mydb.dmp mydb

    on dest:

    createdb -T template1 mydb2

    pg_restore -v -e -d mydb2 mydb.dmp

    • 8