How to import and export a PostgreSQL database

How to import and export a PostgreSQL database is covered in this article. Databases can be imported and exported for many different purposes, such as:

• 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:Copypg_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 Hostvento 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:Copypg_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:
   • If you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page, click phpPgAdmin:
     
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:
   • If you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page, click PostgreSQL Databases:
     
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 Hostvento Hosting account using SCP, SFTP, or FTP.
2. Log in to your Hostvento 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:Copypsql -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:
   • If you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page, click phpPgAdmin:
     
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.