View source for PostgreSQL Database Dump and Restore

=Internal=

* [[PostgreSQL Operations#Subject|PostgreSQL Operations]]

=Overview=

This article describes the dump and restore procedure for a PostgreSQL database. The procedure can be used in various situations, such as when the database needs to be backed up and restored, or transferred into another physical RDBMS. Database export will be performed using the <tt>pg_dump</tt> utility. The import will be performed with <tt>psql</tt>.

=Procedure=

==Dump the Database==

===Get Access to Host Database is Running On===

<tt>pg_dump</tt> utility needs to run on the host the database to be dumped runs on.

===Dump the Database===

While the database is running (<font color=red>do we need to drop connections and prevent new connections being accepted while doing it?</font>) execute the following command:

 pg_dump -U ''database-user-name'' ''database-name'' > ''database-name''-''user-name''-export.pgsql

The <tt>''database-name''-''user-name''-export.pgsql</tt> file now contains all of the data for the ''database-name'' database.

==Import the Database==

===Get Access to Host Database is Running On===

It is preferable if <tt>psql</tt> utility runs on the database host while the import is being performed.

Transfer the dump file locally.

===Create the Empty Database===

If the database we're importing into does not exist, it has to be created before the import starts. This is how a database is created: {{Internal|PostgreSQL_DDL_Operations#Create_a_Database|Create a Database}}

===Create the User===

If the user we are importing on behalf of does not exist, it will have to be created. This is how a user is created: {{Internal|PostgreSQL_DDL_Operations#Create_User|Create a User}}

Also, the user should be granted sufficient permissions: {{Internal|PostgreSQL_DDL_Operations#Granting_Privileges_on_a_Database|Granting Privileges on a Database}}

<font color=red>How is the password handled when recreating the user? Will the import procedure restore it?</font>

===Import the Database===

 psql -U ''database-user-name'' ''database-name'' <  ./''database-name''-''user-name''-export.pgsql