Summary
PostgreSQL offers native ways to export database as backup from existing server and import to restore it to another one.
This post shows how to do it. It is really simple thanks to them.
It consists of a pair of key commands.
Export (backup)
pg_dump dbname > dumpfile
Import (restoration)
psql dbname < dumpfile
You must create database where you migrate to beforehand.
Environment
Tutorial
Create database on new server
Create user
The user name must be the same to the owner of existing database. It is shown as {DB_OWNER} below.
$ createuser -U {DB_ADMIN} --port {DB_PORT} --pwprompt {DB_OWNER}
You will be asked of the password of the new user (role):
Enter password for new role:
Enter it again:
Then the password of {DB_ADMIN} (the database administrator such as postgres) will be required:
Password:
Create database
As to below, {DB_OWNER} is that you created above. {DB_NAME} is the very database you are migrating.
Run:
$ createdb -U {DB_ADMIN} --port {DB_PORT} --encoding=UTF8 --locale=C --template=template0 --owner={DB_OWNER} {DB_NAME}
Then the password of {DB_ADMIN} will be required again:
Password:
Now your new database is ready.
Migrate
Export
Get the data from {DB_NAME} on {OLD_SERVER} and write it in {EXPORT_FILE}.sql:
$ pg_dump -U {DB_OWNER} -h {OLD_SERVER} {DB_NAME} > {EXPORTED_FILE}.sql
Next, I compressed the exported file and move it to the new server instead.
(Alternative) Direct import from the old server
Alternatively, you might import it to the new server directly with -h option:
$ psql -U {DB_OWNER} -h {NEW_SERVER} {DB_NAME} < {EXPORTED_FILE}.sql
Transfer
When it is big, you had better compress it before moving it.
$ gzip {EXPORTED_FILE}.sql
Then deliver it to the new server with scp or another.
$ scp {EXPORTED_FILE}.sql.gz new-server
Transfer is successful ? Enter the new server, and decompress it.
$ gunzip {EXPORTED_FILE}.sql.gz
Import
It’s the final step !! Supposed you are in the new server, run:
$ psql -U {DB_OWNER} -d {DB_NAME} -f {EXPORTED_FILE}.sql
The password will be required:
Password for user {DB_OWNER}:
The output was in my case:
SET
(...)
set_config
------------
(1 row)
SET
CREATE FUNCTION
(...)
CREATE TABLE
CREATE SEQUENCE
ALTER TABLE
(...)
COPY 20
(...)
setval
--------
6820
(1 row)
(...)
ALTER TABLE
(...)
Reference
With many thanks to:
Happy storing 🕊