43

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?

Pablo
  • 7,249
  • 25
  • 68
  • 83

3 Answers3

45

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.

Pablo
  • 7,249
  • 25
  • 68
  • 83
11

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.

voretaq7
  • 79,345
  • 17
  • 128
  • 213
  • Thank you. I'll give -d a try with pg_restore, but I'm for the moment, dumping it as text and in pg_dump I'm not specifying neither -c nor -C. I'm just calling pg_dump databasename. Am I missing something? – Pablo Jan 28 '11 at 16:45
  • with no options `pg_dump` should create a dump that you restore into whatever database you're connected to (it's been a while since I did a text dump & restored to a different DB, but if there's no `CREATE` & `\connect` bits the SQL in the text dump gets executed wherever you are when you `\i` mport the dump file. – voretaq7 Jan 28 '11 at 16:49
  • pg_dump in text or binary mode, by default, has tons of mentions of the database name. – Pablo Jan 29 '11 at 15:26
8

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

Dmitry Kremer
  • 81
  • 1
  • 1
  • 3
    This syntax worked for me: `pg_dump -v -Fc mydb > mydb.dmp` (pg_dump v9.4.5). – Paolo Dec 19 '15 at 17:36
  • created a new database to restore it in, nevertheless I get a : `could not execute query: ERROR: schema "public" already exists` – bvdb Jul 06 '20 at 09:39