How to import and export a PostgreSQL database

This article describes how to import and export a PostgreSQL database. You can import and export databases for a variety of scenarios, including:

  • Transferring a database from one web hosting account or provider to another.
  • Importing a third-party database.
  • Backing up a database.

Exporting a PostgreSQL database

You can export a PostgreSQL database to a file by using the pg_dump command line program, or you can use phpPgAdmin.

Method #1: Use the pg_dump program

To export a PostgreSQL database using the pg_dump program, follow these steps:

  1. Access the command line on the computer where the database is stored. For example, if the database is on another web hosting account or with another web hosting provider, log in to the account using SSH. If you have physical access to the computer, you can open a DOS or terminal window to access the command line.
  2. Type the following command, and then press Enter. Replace dbusername with a username that has permissions to access the database, and replace dbname with the name of the database that you want to export:
    pg_dump -U dbusername dbname > dbexport.pgsql
    
    This article uses the dbexport.pgsql filename for the exported database, but you can name the file anything you want.
  3. Type your A2 Hosting account password at the Password prompt.
  4. The dbexport.pgsql file now contains all of the data for the dbname database. If the dbexport.pgsql file is on a remote computer, download the file to your local computer.

    You may receive the following error messages when you try to export a database:

    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
    

    These errors occur because some server database templates include PostGIS with restricted access permissions. To export a PostgreSQL database without this data, type the following command instead of the command listed in step 2:

    pg_dump -U dbusername dbname -N topology -T spatial_ref_sys > dbexport.pgsql
    
Method #2: Use phpPgAdmin

To export a PostgreSQL database using phpPgAdmin, follow these steps:

  1. Log in to cPanel.
    If you do not know how to log in to your cPanel account, please see this article.
  2. Open phpPgAdmin:
    • If you are using the Jupiter theme, on the Tools page, in the Databases section, click phpPgAdmin:

      cPanel - Databases - phpPgAdmin icon

    • If you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page, click phpPgAdmin:

      cPanel - Databases - phpPgAdmin icon

  3. The phpPgAdmin administration page appears in a new window. In the left pane of the phpPgAdmin window, expand Servers, expand PostgreSQL, and then click the name of the database that you want to export.
  4. On the top menu bar, click Export.
  5. Under Format, click Structure and data.
  6. Under Options, in the Format list box, select SQL.
  7. Under Options, click Download.
  8. Click Export.
  9. In the file dialog, select a location to save the file, and then click Save.

Creating a new PostgreSQL database and assigning a user

Before you can import the database, you must create a new database in cPanel and assign a user to it. To do this, follow these steps:

  1. Log in to cPanel.
    If you do not know how to log in to your cPanel account, please see this article.
  2. Open the PostgreSQL Databases tool:
    • If you are using the Jupiter theme, on the Tools page, in the Databases section, click PostgreSQL Databases:

      cPanel - Databases - PostgreSQL Databases icon

    • If you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page, click PostgreSQL Databases:

      cPanel - Databases - PostgreSQL Databases icon

  3. Under Create New Database, in the Database Name text box, type the name of the database.

    You cannot use capital letters in a PostgreSQL database name.
  4. Click Create Database.
  5. When the database is created, click Go Back.
  6. Under Add User to Database, in the User list box, select the user that you want to add.
  7. In the Database list box, select the new database.
  8. Click Submit.

Importing a PostgreSQL database

After you have created a new database in cPanel, you can import the database's contents by using the psql command line program, or you can phpPgAdmin.

You should import all PostgreSQL data as the primary PostgreSQL user (that is, by using your domain username). If you import PostgreSQL data as a regular user, you will be unable to see or manipulate the data properly using phpPgAdmin. After you have imported the data as the primary PostgreSQL user, you can grant a regular user access to the data. Then you do not have to use the primary domain username and password in scripts that access the database.
Method #1: Use the psql program

To import a PostgreSQL database using the psql program, follow these steps:

  1. Transfer the dbexport.pgsql file to your A2 Hosting account using SCP, SFTP, or FTP.
  2. Log in to your A2 Hosting SSH account.
  3. Type the following command, and then press Enter. Replace username with your username and replace dbname with the name of the database that you want to import the data into:
    psql -U username dbname < dbexport.pgsql
    
  4. The dbname database should now contain the data that is in the dbexport.pgsql file.
Method #2: Use phpPgAdmin

To import a PostgreSQL database using phpPgAdmin, follow these steps:

  1. Log in to cPanel.
    If you do not know how to log in to your cPanel account, please see this article.
  2. Open phpPgAdmin:
    • If you are using the Jupiter theme, on the Tools page, in the Databases section, click phpPgAdmin:

      cPanel - Databases - phpPgAdmin icon

    • If you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page, click phpPgAdmin:

      cPanel - Databases - phpPgAdmin icon

  3. The phpPgAdmin administration page appears in a new window. In the left pane of the phpPgAdmin window, expand Servers, expand PostgreSQL, and then click the name of the database that you want to import the data into.
  4. On the top menu bar, click SQL.

    The SQL link is located between the Schemas and Find links.
  5. Click Choose File.
  6. In the file dialog, select where the import file is located, and then click Open.
  7. Click Execute. phpPgAdmin imports the data from the file into the database.

More Information

Did you find this article helpful? Then you'll love our support. Experience the A2 Hosting difference today and get a pre-secured, pre-optimized website. Check out our web hosting plans today.

We use cookies to personalize the website for you and to analyze the use of our website. You consent to this by clicking on "I consent" or by continuing your use of this website. Further information about cookies can be found in our Privacy Policy.