Importing and Exporting PostgreSQL Databases

Learn how to export a PostgreSQL database for backup or migration purposes, and how to import it into a new hosting account.

This guide covers common scenarios such as:

• Migrating a database between hosting accounts or providers
• Importing a third-party database
• Creating a manual backup

Exporting a PostgreSQL Database

You can export a PostgreSQL database using either the pg_dump command-line utility or phpPgAdmin.

Method 1: Export Using pg_dump

1. Access the command line on the server where the database is stored.
   • If the database lives on another hosting account, connect via SSH.
   • If you have direct access to the machine, open a terminal window.
2. Run the export command:

   pg_dump -U dbusername dbname > dbexport.pgsql
   

   Replace dbusername with a user that has access permissions to the database, and dbname with the name of the database you’re exporting.

   Note: The filename dbexport.pgsql is just an example — you can name the export file anything you like.

3. Enter your account password when prompted.
4. Once complete, dbexport.pgsql will contain the full contents of the database. If the file is on a remote server, download it to your local machine before proceeding.

Troubleshooting Permission Errors

If your database template includes PostGIS, you may see an error like this during export:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for schema topology
pg_dump: The command was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

This happens because PostGIS restricts access to its topology schema by default. To exclude that data and complete the export successfully, run:

pg_dump -U dbusername dbname -N topology -T spatial_ref_sys > dbexport.pgsql

Method 2: Export Using phpPgAdmin

1. Log in to cPanel.
2. Under Tools > Databases, click phpPgAdmin.
3. In the left-hand panel, expand Servers > PostgreSQL, then select the database you want to export.
4. Click Export in the top menu.
5. Under Format, select Structure and data.
6. Under Options, set the format to SQL and enable Download.
7. Click Export, then choose a save location for the file.

Creating a New PostgreSQL Database

Before importing, you’ll need to create a destination database in cPanel and assign a user to it.

1. Log in to cPanel.
2. Under Tools > Databases, click PostgreSQL Databases.
3. Under Create New Database, enter a database name and click Create Database.
   

   Note: PostgreSQL database names cannot contain capital letters.

4. Click Go Back once the database is created.
5. Under Add User to Database, select an existing user and the new database, then click Submit.

Importing a PostgreSQL Database

Important: Always import data using the primary PostgreSQL user (your domain username), not a regular user account. Importing as a regular user can prevent proper viewing and management of the data in phpPgAdmin. Once the import is complete under the primary user, you can grant access to a regular user — avoiding the need to use primary credentials in application scripts.

Method 1: Import Using psql

1. Transfer dbexport.pgsql to your hosting account using SCP, SFTP, or FTP.
2. Log in via SSH.
3. Run the import command:

   psql -U username dbname < dbexport.pgsql
   

   Replace username with your account username and dbname with the destination database name.

4. Once complete, dbname will contain the imported data.

Method 2: Import Using phpPgAdmin

1. Log in to cPanel.
2. Under Tools > Databases, click phpPgAdmin.
3. Expand Servers > PostgreSQL, then select the destination database.
4. Click SQL in the top menu (located between Schemas and Find).
5. Click Choose File and select your export file.
6. Click Execute to run the import.

Troubleshooting Common Import Errors

Error: syntax error at or near "\" (\restrict / \unrestrict)

If you import via phpPgAdmin and see an error similar to this:

backup.sql:10: ERROR: syntax error at or near "\"
LINE 1: \restrict 5zM29iH2k7akS5nErpj72n3YbjZjzmEuLIuhyjJCWILEJn30iq...

Cause: As of pg_dump versions 13.22, 14.19, 15.14, 16.10, and 17.6+, dump files include \restrict and \unrestrict lines at the top and bottom of the file. These were added as a security fix (CVE-2025-8714) to prevent a malicious server from injecting commands that psql would otherwise execute. They’re valid psql meta-commands, not SQL — so any tool that runs the file as plain SQL rather than through the psql client, like phpPgAdmin’s SQL executor, will fail on them.

Fix — Option 1: Import using psql instead

The psql command-line client understands these meta-commands natively, so importing the file unmodified works without any changes:

psql -U username dbname < dbexport.pgsql

See Method 1: Import Using psql above.

Fix — Option 2: Strip the lines and import via phpPgAdmin

If you need to stay in phpPgAdmin, remove the \restrict and \unrestrict lines from the file before uploading. On a system with SSH access:

grep -v -E '^\\(restrict|unrestrict)' dbexport.pgsql > dbexport_clean.pgsql

Then upload dbexport_clean.pgsql through the phpPgAdmin SQL tab instead of the original file.

Note: These lines only wrap the file for safety during a psql-driven restore — removing them doesn’t affect the schema or data in the dump.

Error: could not connect to server: No such file or directory (Unix socket)

If you run pg_dump without specifying connection details and see something like:

pg_dump: error: connection to database "jakariak" failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Cause: Running pg_dump with no arguments tells it to connect using your OS username as both the database user and the database name, over a local Unix domain socket at the default path. On shared hosting environments (including cPanel), PostgreSQL is typically configured to accept connections over TCP/IP only — it either isn’t listening on that Unix socket path at all, or the socket lives somewhere other than the default location. Either way, the connection attempt fails before it ever reaches the actual database or credentials check.

Fix: Always connect explicitly using -h (host) along with -U (username) and the database name, rather than relying on the defaults:

pg_dump -h localhost -U dbusername dbname > dbexport.pgsql

If localhost still fails, try the loopback IP address instead, which forces a TCP connection rather than a socket lookup:

pg_dump -h 127.0.0.1 -U dbusername dbname > dbexport.pgsql

You’ll be prompted for the password for dbusername as usual.

Tip: This same fix applies to psql if you hit an identical socket error while importing — add -h localhost (or -h 127.0.0.1) to the command.

Additional Resources

• psql documentation (PostgreSQL 9.5)
• psql documentation (PostgreSQL 9.6)
• pg_dump documentation (PostgreSQL 9.5)
• pg_dump documentation (PostgreSQL 9.6)