PostgreSQL Database Dump and Restore: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(9 intermediate revisions by the same user not shown)
Line 5: Line 5:
=Overview=
=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 back up, 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>.
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=
=Procedure=
Line 33: Line 33:
===Create the Empty Database===
===Create the Empty Database===


===Crete the User
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}}


===Import the 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}}


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:
<font color=red>How is the password handled when recreating the user? Will the import procedure restore it?</font>


pg_dump -U ''database-user-name'' ''database-name'' > ''database-name''-''user-name''-export.pgsql
===Import the Database===


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

Latest revision as of 19:33, 21 December 2018

Internal

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 pg_dump utility. The import will be performed with psql.

Procedure

Dump the Database

Get Access to Host Database is Running On

pg_dump utility needs to run on the host the database to be dumped runs on.

Dump the Database

While the database is running (do we need to drop connections and prevent new connections being accepted while doing it?) execute the following command:

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

The database-name-user-name-export.pgsql 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 psql 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:

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:

Create a User

Also, the user should be granted sufficient permissions:

Granting Privileges on a Database

How is the password handled when recreating the user? Will the import procedure restore it?

Import the Database

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