How to backup and restore a PostgreSQL database

Before upgrading Pyramid, it is strongly advised to take a backup before hand.    If using the built in PostgreSQL for the Pyramid repository database  below is how to backup and restore the database if needed via the windows command line.

To backup the database, the following command should be run from an elevated command prompt on the server where Pyramid has been installed. 

{installdir}\postgres\pgsql\bin\pg_dump.exe -U {uid} -h {host_server} -p {port} -F t  -f  "{windows_folder_desktopdirectory}\pyramidDbBackup.tar"  {dbname}

uid is  “pyramid”
port "12130"
dbname is  “pyramidg2”
password  “pyramid”

Example command to backup the database, where the Pyramid server is called "MyBIServer" and Pyramid was installed on the C drive.

"C:\Program Files\Pyramid 2018"\postgres\pgsql\bin\pg_dump.exe -U pyramid  -h localhost -p 12130 -F t  -f  "C:\backup\pyramidDbBackup.tarpyramidg2

If you need to revert back to a previous version of Pyramid, first uninstall* the upgrade version and re-install the previous version. Then delete the database created and restore the backed up one.

To delete the database (stop all Pyramid services first):

{installdir}\postgres\pgsql\bin\psql psql -h {host_server} -d postgres -U {uid} -p {port} -c "drop database {dbname} --force;"

Example command to delete the database where the Pyramid server is called "MyBIServer" and Pyramid was installed on the C drive.

"C:\Program Files\Pyramid 2018"\postgres\pgsql\bin\psql -h localhost -d postgres -U pyramid -p 12130 -c "drop database pyramidg2 --force;"

Before the database is restored, the database must first be created. To do this use the following example command where the server that Pyramid is installed on is called "MyBIServer"

"C:\Program Files\Pyramid 2018"\postgres\pgsql\bin\psql -h localhost -d postgres -U pyramid -p 12130 -c "create database pyramidg2;"

To restore the database:

{installdir}\postgres\pgsql\bin\pg_restore.exe -h {host_server} -p {port} -U {uid} -d {dbname}  "{windows_folder_desktopdirectory}\pyramidDbBackup.tar"  {dbname}

Example command to restore the database where the Pyramid server is called "MyBIServer", Pyramid was installed on the C drive and the back is called "pyramidDbBackup.tar"

"C:\Program Files\Pyramid 2018\postgres\pgsql\bin\pg_restore.exe" -h localhost -p 12130 -U pyramid -d pyramidg2 C:\backup\pyramidDbBackup.tar

*When uninstalling a single install of Pyramid, make sure to backup the IMDB databases, whose default location is here: "C:\Program Files\Pyramid 2018\repository\imdata"  

You may also want to backup the publication folder whose default location is here:

"C:\Program Files\Pyramid 2018\repository\general"

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like1 Follow
  • 1 mth agoLast active
  • 909Views
  • 1 Following