Search code examples
sqldatabasepostgresqldatabase-backupsdatabase-restore

Create database explicitly before restoring to it?

When I setup my PostgreSQL server one of the first things I will do is import a database for an external source. Which of the following is the right way to do it?

  • Create a database called "NEWDB" on the PostgreSQL server and then import my external "BACKUPDB" database from my pg_dump into the "NEWDB".

  • Don't create a database on the PostgreSQL server, and import the "NEWDB" database, thereby automatically creating "NEWDB" on the postgresql server.

I guess my question is, if I want to import an existing database to the PostgreSQL server, do I first need to create a database for it to go into?

Solution

  • You don't have to. It depends on what you want to achieve. If you dump a single database with pg_dump, CREATE DATABASE and ALTER DATABASE commands are not included. You are expected to connect to an existing database. So you have to create it first.
    I quote advice from the manual:

    If your database cluster has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example:

    CREATE DATABASE foo WITH TEMPLATE template0;

    And also:

    The dump file also does not contain any ALTER DATABASE ... SET commands; these settings are dumped by pg_dumpall, along with database users and other installation-wide settings.

    pg_dumpall, on the other hand, dumps the whole DB cluster including meta-objects like users. It includes CREATE DATABASE statements and connects to each DB when restoring. You can even include DROP DATABASE statements with the -c (--clean) option. Careful with that.

    Every instance of PostgreSQL has a default maintenance db named "postgres" that you can connect to - to create databases for instance or start a full restore (from pg_dumpall). But a single-DB dump (from pg_dump) has to be run against its target database.

    Finally:

    Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You can also run vacuumdb -a -z to analyze all databases.