How to backup and restore a PostgreSQL database

Before upgrading Pyramid, it is strongly recommended to back up the Pyramid repository database.

This article explains how to back up and restore a PostgreSQL repository database using the command line on Windows and Linux.

Important for Pyramid 2025.11 and later: Pyramid no longer supports PostgreSQL 12 or earlier for its repository database. Customers previously using the bundled PostgreSQL 9.6 database must migrate to a supported PostgreSQL version before upgrading to Pyramid 2025.11 or later.

The migration guides use PostgreSQL 18, which is installed and managed separately from Pyramid. The standard PostgreSQL port is 5432, rather than the port 12130 used by the older bundled database.

For detailed migration instructions, see:

• Migrating PostgreSQL 9.6 to PostgreSQL 18 - Windows
• Migrating PostgreSQL 9.6 to PostgreSQL 18 - Linux

1. Identify the PostgreSQL Connection Details

Before running any commands, identify the database name, host, port, username, and password.

These can be found in the Pyramid config.ini file, located in the Pyramid installation directory.

Default Windows location:

C:\Program Files\Pyramid\config.ini

Default Linux location:

/opt/pyramid/config.ini

Check the [data] section for the repository connection settings.

Database Name and Password

In modern Pyramid installations, the built-in PostgreSQL database name is normally generated automatically rather than using the older database name pyramidg2.

For installations using the default generated credentials, the database name starts with pyramid followed by an alphanumeric sequence.

The default database password is generated by inserting an exclamation mark (!) immediately after the word pyramid.

Example:

• Database name: pyramid2324f
• Username: pyramid
• Password: pyramid!2324f

These values are examples only. Always verify the actual repository connection settings. If the password has been changed manually, use the configured password instead.

PostgreSQL Port and Installation Location

The connection settings depend on whether you are using the older bundled PostgreSQL database or a separately installed PostgreSQL server.

Important: After migrating from bundled PostgreSQL to PostgreSQL 18, the database name, username, and password should remain unchanged if the documented migration procedure was followed.

The port will normally change from 12130 to 5432. Always confirm the actual value in config.ini, as installations may use a custom port.

On Linux, the PostgreSQL 18 executable location depends on the Linux distribution and installation method. Use the installed PostgreSQL 18 utilities, rather than assuming they are still located inside the Pyramid installation directory.

2. Backing Up the PostgreSQL Database

Use the PostgreSQL pg_dump utility to create a database backup.

The following examples use:

• Database name: pyramid2324f
• Database username: pyramid
• Host: localhost
• Backup file: pyramidDbBackup.tar

Replace the example values with those used in your environment.

Windows - Older Bundled PostgreSQL

For installations still using Pyramid's older bundled PostgreSQL database on port 12130:

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

Windows - PostgreSQL 18

For installations that have migrated to PostgreSQL 18, use the PostgreSQL utilities from the separate PostgreSQL installation.

For example:

"C:\Program Files\PostgreSQL\18\bin\pg_dump.exe" -U pyramid -h localhost -p 5432 -F t -f "C:\backup\pyramidDbBackup.tar" pyramid2324f

Ensure that C:\backup exists before running the command.

Linux - Older Bundled PostgreSQL

/opt/pyramid/postgres/pgsql/bin/pg_dump -U pyramid -h localhost -p 12130 -F t -f /home/master/pyramidDbBackup.tar pyramid2324f

Linux - PostgreSQL 18

When PostgreSQL 18 has been installed separately, use its pg_dump utility.

If the PostgreSQL 18 utilities are available in the system PATH, an example command is:

pg_dump -U pyramid -h localhost -p 5432 -F t -f /home/master/pyramidDbBackup.tar pyramid2324f

If multiple PostgreSQL versions are installed, use the full path to the PostgreSQL 18 utility to ensure that the correct version is used.

When prompted for the database password, enter the actual configured password.

Important: A backup should be stored in a secure location outside the Pyramid installation directory, preferably with a copy on another server or backup storage system.

3. Restoring the PostgreSQL Database

The following instructions describe restoring a TAR-format backup using PostgreSQL command-line utilities.

Warning: Deleting or replacing a database is destructive. Ensure you have a verified backup and have identified the correct target database before proceeding.

Before restoring:

1. Stop all Pyramid application services that connect to the repository.
2. Leave the PostgreSQL database service running.
3. Verify that the backup file exists and is valid.
4. Confirm the PostgreSQL version, port and database name.
5. Ensure the database user has the necessary permissions.

Step 1 - Delete the Existing Database

If the existing database must be completely replaced, it can be deleted using the DROP DATABASE command.

The following commands use the PostgreSQL 13+ syntax with WITH (FORCE). The older bundled PostgreSQL 9.6 does not support this syntax; active connections must be terminated using a compatible method first.

Windows - PostgreSQL 18

"C:\Program Files\PostgreSQL\18\bin\psql.exe" -h localhost -d postgres -U pyramid -p 5432 -c "DROP DATABASE pyramid2324f WITH (FORCE);"

Linux - PostgreSQL 18

psql -h localhost -d postgres -U pyramid -p 5432 -c "DROP DATABASE pyramid2324f WITH (FORCE);"

These examples assume that the PostgreSQL user has permission to drop the database. Otherwise, use an appropriately privileged PostgreSQL account.

For older bundled PostgreSQL versions, use the appropriate older-version procedure to terminate active connections and then run:

DROP DATABASE pyramid2324f;

Step 2 - Create the Database

After deleting the original database, create a new empty database with the same name.

Windows - PostgreSQL 18

"C:\Program Files\PostgreSQL\18\bin\psql.exe" -h localhost -d postgres -U pyramid -p 5432 -c "CREATE DATABASE pyramid2324f OWNER pyramid;"

Linux - PostgreSQL 18

psql -h localhost -d postgres -U pyramid -p 5432 -c "CREATE DATABASE pyramid2324f OWNER pyramid;"

For older bundled PostgreSQL installations, use the corresponding psql executable and port 12130.

The commands assume the role pyramid already exists. If necessary, use a PostgreSQL administrative account to create the database with the required ownership.

Step 3 - Restore the Backup

Use pg_restore to restore the contents of the TAR backup into the newly created database.

Windows - Older Bundled PostgreSQL

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

Windows - PostgreSQL 18

"C:\Program Files\PostgreSQL\18\bin\pg_restore.exe" -h localhost -p 5432 -U pyramid -d pyramid2324f -v "C:\backup\pyramidDbBackup.tar"

Linux - Older Bundled PostgreSQL

/opt/pyramid/postgres/pgsql/bin/pg_restore -h localhost -p 12130 -U pyramid -d pyramid2324f -v /home/master/pyramidDbBackup.tar

Linux - PostgreSQL 18

pg_restore -h localhost -p 5432 -U pyramid -d pyramid2324f -v /home/master/pyramidDbBackup.tar

The -v option enables verbose output to display restoration progress.

Review the output for errors and verify that the expected database objects and tables have been restored.

Note: If the backup references PostgreSQL users or object owners that do not exist on the destination server, the relevant roles must be created or appropriate restore options used.

4. Important Considerations for Pyramid 2025.11 and Later

Pyramid 2025.11 no longer supports PostgreSQL versions 12 or earlier as its repository database.

Customers who previously installed Pyramid using the New Internal Repository option with the older bundled PostgreSQL 9.6 database must complete the PostgreSQL migration before upgrading Pyramid.

The recommended migration guides use PostgreSQL 18. This involves installing PostgreSQL separately, migrating the existing repository database, and changing the repository connection to use the new PostgreSQL instance.

The documented procedure also requires:

• Keeping the existing repository database name and credentials.
• Updating the repository port in the Pyramid config.ini file.
• Updating config.ini on additional Pyramid servers in distributed deployments.
• Updating the Local PG data source connection in the Pyramid Admin Hub.
• Verifying that Pyramid works correctly before performing the application upgrade.

For installations following the migration guides, the repository port changes from 12130 to 5432.

Important: Do not assume that the PostgreSQL installation remains inside the Pyramid installation folder after migration. Use the executables from the separately installed PostgreSQL server.

Follow the complete instructions in the appropriate migration guide:

• PostgreSQL 9.6 to PostgreSQL 18 Migration - Windows
• PostgreSQL 9.6 to PostgreSQL 18 Migration - Linux

5. Recovering Pyramid After an Upgrade

If a Pyramid upgrade fails and you need to return to a previous version, ensure that you have a repository backup taken before the upgrade.

Important: A Pyramid repository cannot be downgraded in place. Recovery requires restoring a compatible backup and following the appropriate application recovery process.

For more information, see:

Pyramid Application Recovery

How to Revert Back to a Previous Version of Pyramid

6. Additional Files to Back Up

Backing up the PostgreSQL repository does not include all files stored on the Pyramid servers.

Depending on the environment, the following folders should also be considered for backup.

IMDB Data

C:\Program Files\Pyramid\repository\imdata

Contains data used by Pyramid In-Memory Databases.

Publications and Rendered Content

C:\Program Files\Pyramid\repository\general

Contains generated publications and rendered content.

Uploaded Files

C:\Program Files\Pyramid\repository\upload

Contains uploaded files used by relevant Pyramid processes.

Note: These are traditional default Windows locations. The repository data directory may have been configured separately during installation.

7. Alternative Approach Using DBeaver

If you prefer using a graphical interface rather than the command line, PostgreSQL backups and restores can also be performed using DBeaver.

See the following article:

How to Backup and Restore a PostgreSQL Database via DBeaver

8. Backup Recommendations

• Always take a database backup before upgrading Pyramid.
• Use the actual connection settings from config.ini.
• For PostgreSQL 18, use the separately installed PostgreSQL utilities.
• Ensure that backups are stored outside the Pyramid installation directory.
• Test backups periodically by restoring them in a non-production environment.
• Do not delete a production database unless a verified backup exists.
• Confirm that the PostgreSQL server and client versions are compatible.

Note: The commands in this article are examples. Replace the database name, paths, port, host, and credentials with those used in your own environment.