Using pg_dump to take a snapshot of a database

Question

I am trying to setup a script to take a copy of a database from one server to another.

Thanks to this post Copying PostgreSQL database to another server I have found a way to do that.

But what I need to do is change the name of the database during the copy. I have thought about using sed and doing a simple text replace. But I am worried that this could corrupt the database.

Does any one know the proper way of doing this?

As requested here are the commands I am using

pg_dump -C -U remoteuser -h remoteServer dbname | psql -h localhost -U localadmin template1

Craig Ringer · Accepted Answer · 2012-10-18T01:14:01.527

Just restore to a different database. For pg_restore of -Fc dumps from pg_dump's custom format:

createdb newdbname
pg_restore --dbname newdbname database.dump

For SQL-format dumps not created with the -C option to pg_dump:

createdb newdbname
psql -f database_dump.sql newdbname

If you're streaming the dump from a remote host, just omit -f database_dump.sql as the dump data is coming from stdin.

You can't easily CREATE DATABASE in the same command as your restore, because you need to connect to a different DB like template1 in order to create the new DB. So in your example you might:

psql -h localhost -U localadmin template1 -c 'CREATE DATABASE newdb;'
pg_dump -U remoteuser -h remoteServer dbname | psql -h localhost -U localadmin newdb

Note the omission of the -C flag to pg_dump.

The first command is just the longhand way of writing createdb -h localhost -U localadmin newdb.

Update: If you're stuck with a pg_dump created with the -C flag you can indeed just sed the dump so long as you're extremely careful. There should only be four lines (one a comment) at the start of the file that refer to the database name. For the database name "regress" dumped with Pg 9.1's pg_dump -C:

--
-- Name: regress; Type: DATABASE; Schema: -; Owner: craig
--

CREATE DATABASE regress WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


ALTER DATABASE regress OWNER TO craig;

\connect regress

This can be transformed quite safely with three (or four if you want to rewrite the comment) very specific sed commands. Do not just do a global find and replace on the database name, though.

sed \
  -e 's/^CREATE DATABASE regress/CREATE DATABASE newdbname/' \
  -e 's/^ALTER DATABASE regress/ALTER DATABASE newdbname/' \
  -e 's/^\\connect regress/\\connect newdbname/' \
  -e 's/^--Name: regress/--Name: newdbname/'

This should be a last resort; it's much better to just dump without -C.

Using pg_dump to take a snapshot of a database

1 Answers1