Import a .sql File into a PostgreSQL Database

What are we doing?

PostgreSQL, also called Postgres, is a database system. A database stores data in tables, like spreadsheet pages. A .sql file is a text file made by exporting a database. It holds the commands needed to rebuild the tables and fill them with data. Importing runs those commands.

You need an empty database, a user, and the file. If you have none yet, create the database and user first.

Warning: An import can add or overwrite data. Back up the target database first.

Method 1: phpPgAdmin in cPanel

phpPgAdmin is a web tool for managing Postgres. It is part of cPanel on plans that include PostgreSQL. Ask Hostvento support if it is on your plan.

1. Log in to cPanel.
2. In the Databases section, open phpPgAdmin.
3. Click your database name on the left.
4. Click the SQL link at the top.
5. Click Choose File under the upload area and select your .sql file.
6. Click Execute.

When it finishes, open the database. You should see your tables.

Method 2: Command line

SSH lets you type commands on the server. First upload the file with File Manager or SFTP. Then connect with SSH.

1. Go to the folder that holds the file.
2. Run this command. Change the user, database and file name to yours.

psql -U username -d databasename -f backup.sql

This logs in as the user and runs every command in the file against the database.

3. Type the password if asked.
4. Watch the messages. Lines that start with ERROR need a look.

On a server where you have root access, you can run it as the postgres user instead:

su - postgres
psql -d databasename -f /path/to/backup.sql

Use the real path to your file.

Compressed files

If the file ends in .gz, unpack it first.

gunzip backup.sql.gz

This turns it into backup.sql.

Common errors

• Relation already exists: the table is already in the database. Start with an empty one.
• Permission denied: the user lacks rights. Give it all privileges on the database.
• Role does not exist: the file mentions a user that you have not created.

Quick recap

• A .sql file holds commands that rebuild a database.
• Import it with phpPgAdmin or with psql -f.
• Use an empty database and a user with full rights.
• Back up before importing.