Export & import PostgreSQL database

Export or backup from 'corpus user' and 'corpus database'

1. Back up the DB from "corpus user" using the following commands:

   • From <postgres> user, run all the below commands:

     CODE

     -bash-4.2$ psql -U postgres -c "\du"

     The above command lists all the users of the Postgres cluster.
     

     CODE

     -bash-4.2$ psql -U postgres -c "\l"

     The above command will list all the Postgres databases of the Postgres cluster.
     

     CODE

     -bash-4.2$ psql -U postgres -c "\l"

     
     

   • Now, we have to take the backup of the "corpus" user with the below command:
     

     CODE

     -bash-4.2$ pg_dumpall -g | grep -w corpus > /var/lib/pgsql/create-corpus-user.sql

     The above command will dump all the global objects of our Postgres cluster server and fetch only the corpus user command and save it in the create-corpus-user.sql file at the locations "/var/lib/pgsql/" or you can give any of the locations of your choice.
     
     

2. Next, back up the corpus database:
   

   CODE

   -bash-4.2$ pg_dump -U corpus -j4 -v -Fd -f /var/lib/pgsql/corpus_$(date '+%Y-%m-%d_%H%M%S').dir corpus >& /var/lib/pgsql/corpus_$(date '+%Y-%m-%d_%H%M%S').log

   The above command will create a "corpus" database dump with directory format in the current directory with the current date and time stamp included dump log file.
   
   

3. Copy this "create-corpus-usesr.sql" and backup dump directory (like corpus_2018-01-22_063713.dir) on the target server/path where you want to import/refresh the "corpus" database.

Import/restore/refresh Corpus database on the new server:

1. Before importing the corpus database we need to create a corpus user with the "create-corpus-user.sql":
   "create-corpus-user.sql" creates a corpus user with the same password which is used on the server from where we have taken the export of corpus user.

2. Execute the following command from Postgres user to create a "corpus as a user" on a new Postgres cluster where you want to import/refresh the corpus database:
   

   CODE

   -bash-4.2$ psql -U postgres -f create-corpus-user.sql

   
   Above, the command will show the result like as below:
   

   CODE

   -bash-4.2$ psql -U postgres -f create-corpus-user.sql CREATE ROLE ALTER ROLE -bash-4.2$

3. Now import/refresh the corpus database through "pg_restore" with the following command:
   

   CODE

   -bash-4.2$ pg_restore -v -j4 -c -C -d postgres /var/lib/pgsql/NAME_OF _YOUR DIRECTORY_DUMP_FILE >& corpus_restore_$(date '+%Y-%m-%d_%H%M%S').log The second command: will restore/refresh corpus database with CAST and owner as corpus on the new serverFor example: -bash-4.2$ pg_restore -v -j4 -c -C -d postgres /var/lib/pgsql/corpus_2018-01-22_063713.dir >& corpus_restore_$(date '+%Y-%m-%d_%H%M%S').log

   
   

4. The above command restores/refreshes the corpus database including CAST and owner as corpus on the new server.