Postgresql 10 Backup & Restore

1. Connect to VPN

$ sudo vpnc rsvpn

2. Connect to Server

$ ssh dbserver1

3. Get Root and Execute pg_dump

-- switch to root
$ sudo su -
-- execute the pg_dump
# pg_dump -U postgres -Fc database-instance-name > date-database-instance-name.dump

More Info About how to Form the pg_dump Command:

4. Create a New Blank Database to Restore the dump File into

-- switch to postgres user
# su - postgres
-- invoke interactive terminal
$ psql
-- create the new database
postgres-# CREATE DATABASE new_db;
-- We can verify creation by listing current DBs with:
postgres-# \l
-- exit psql
postgres=# \q
-bash-4.1$ exit
logout
-- execute pg_restore as root
# pg_restore -U postgres -d new_db /root/dumps/20180706-db1.dump

More Info About how to Form the pg_restore Command:

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store