PostgreSQL Database Dump and Restore: Difference between revisions
| Line 24: | Line 24: | ||
==Import the 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. | |||
===Import 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. | |||
Revision as of 18:28, 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 back up, 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.
Import 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.