> ## Documentation Index
> Fetch the complete documentation index at: https://kb.hosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Importing and exporting PostgreSQL databases

> Discover how to import and export PostgreSQL databases for easy transfers and backups.

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:

   ```bash theme={null}
   pg_dump -U dbusername dbname > dbexport.pgsql
   ```

   > 📘 Note
   >
   > This article uses the *dbexport.pgsql* filename for the exported database, but you can name the file anything you want.

3. Type your hosting.com 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.

   > 📘 Note
   >
   > 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:
   >
   > ```bash theme={null}
   > 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.
   > 📘 Note
   >
   > If you do not know how to log in to your cPanel account, please see [this article](/docs/accessing-cpanel).

2. On the **Tools** page, in the **Databases** section, click **phpPgAdmin**:\
   ![cPanel - Databases - phpPgAdmin icon](https://static.hosting.com/kb/kb-cpanel-jupiter-databases-phppgadmin-icon.png)

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.
   > 📘 Note
   >
   > If you do not know how to log in to your cPanel account, please see [this article](/docs/accessing-cpanel).

2. On the **Tools** page, in the **Databases** section, click **PostgreSQL Databases**:\
   ![cPanel - Databases - PostgreSQL Databases icon](https://static.hosting.com/kb/kb-cpanel-jupiter-databases-postgresql-databases-icon.png)

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

   > 📘 Note
   >
   > 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.

<Warning>
  **Important**

  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.
</Warning>

### 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 hosting.com account using SCP, SFTP, or FTP.

2. Log in to your hosting.com account [using SSH](/docs/using-ssh-secure-shell).

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:

   ```bash theme={null}
   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.
   > 📘 Note
   >
   > If you do not know how to log in to your cPanel account, please see [this article](/docs/accessing-cpanel).

2. On the **Tools** page, in the **Databases** section, click **phpPgAdmin**:\
   ![cPanel - Databases - phpPgAdmin icon](https://static.hosting.com/kb/kb-cpanel-jupiter-databases-phppgadmin-icon.png)

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**.

   > 📘 Note
   >
   > 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

* To view the online documentation for psql in PostgreSQL 9.5, please visit [https://www.postgresql.org/docs/9.5/app-psql.html](http://www.postgresql.org/docs/8.4/static/app-psql.html).

* To view the online documentation for psql in PostgreSQL 9.6, please visit [https://www.postgresql.org/docs/9.6/app-psql.html](http://www.postgresql.org/docs/9.1/static/app-psql.html).

* To view the online documentation for pg\_dump in PostgreSQL 9.5, please visit [https://www.postgresql.org/docs/9.5/app-pgdump.html](http://www.postgresql.org/docs/8.4/static/app-pgdump.html).

* To view the online documentation for pg\_dump in PostgreSQL 9.6, please visit [https://www.postgresql.org/docs/9.6/app-pgdump.html](http://www.postgresql.org/docs/9.1/static/app-pgdump.html).

## Related articles

* [Connecting to PostgreSQL using psql](/docs/connect-to-postgresql-from-the-command-line)