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.
Backup
To backup the database, the following command should be run from an elevated command prompt on the server where Pyramid has been installed.
When opening command prompt, choose "run as administrator"
**In many cases the pyramid DB name will not be pyramidg2. To check what it is, look at the config.ini file that can be found in the Pyramid install directory. If the Pyramid database is not called pyramidg2, then see this article for what the password will be**
{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 Backup
Assume that the Pyramid server is called "MyBIServer" and Pyramid was installed on the C drive for Windows.
On Windows
"C:\Program Files\Pyramid\postgres\pgsql\bin\pg_dump.exe" -U pyramid -h localhost -p 12130 -F t -f "C:\backup\pyramidDbBackup.tar" pyramidg2
On Linux
/opt/Pyramid/postgres/pgsql/bin/pg_dump -U pyramid -h localhost -p 12130 -F t -f /home/master/pyramidDBBackup.tar pyramidg2
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 Database Delete
Assume the Pyramid server is called "MyBIServer" and Pyramid was installed on the C drive.
On Windows
"C:\Program Files\Pyramid\postgres\pgsql\bin\psql" -h localhost -d postgres -U pyramid -p 12130 -c "drop database pyramidg2 --force;"
On Linux
/opt/Pyramid/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\postgres\pgsql\bin\psql" -h localhost -d postgres -U pyramid -p 12130 -c "create database pyramidg2;"
Restore
{installdir}\postgres\pgsql\bin\pg_restore.exe -h {host_server} -p {port} -U {uid} -d {dbname} "{windows_folder_desktopdirectory}\pyramidDbBackup.tar"
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"
On Windows
"C:\Program Files\Pyramid\postgres\pgsql\bin\pg_restore.exe" -h localhost -p 12130 -U pyramid -d pyramidg2 C:\backup\pyramidDbBackup.tar
On Linux
/opt/Pyramid/postgres/pgsql/bin/pg_restore -h localhost -p 12130 -U pyramid -d pyramidg2 /home/master/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\repository\imdata"
You may also want to backup the publication folder whose default location is here:
"C:\Program Files\Pyramid\repository\general"
Alternative Approach with UI
For those wanting to backup and restore the DB using Dbeaver, the article below describe how to do that: https://community.pyramidanalytics.com/moderate/t/h7hk07w
Reply
Content aside
- 1 Likes
- 2 yrs agoLast active
- 4200Views
- 1 Following