How To Import and Export PostgreSQL Databases

Importing and exporting data from PostgreSQL databases can be critical when you’re trying to move your website to a new server or when you’re trying to back it up. Doing it is not that difficult.
So, let’s get started.

Exporting PostgreSQL Databases

Exporting your Postgres database helps you download your data for safe-keeping or transfer it to another server. You could export the database in two ways.

And, here’s how.

Method 1: via The Command Line

You can export a Postgres database via the command line by running the pg_dump command. Follow these steps to get started.

Install Postgres on your local computer to access your Postgres databases remotely. Of course, ensure to install only the latest stable version.

Open the command line to run tasks on your Postgres database. But if you’ve physical access to the server, open a DOS or terminal window to access the command line.

Run this command to export any database.

pg_dump -U username dbname > dbbackup.pgsql

You’ll need to replace the username, dbname, and dbbackup.pgsql placeholders with the account username, database name, and the desired output file’s name, respectively.

Type your password when prompted to run the database export.

The above command means you want to export data from a database called dbname on a local PostgreSQL server into a file called dbbackup.pgsql.

You can use FTP to download the file.

Method 2: via phpPgAdmin

Managing Postgres databases via the phpPgAdmin is much easier since you won’t need to use the command-line.

phpPgAdmin is Postgres’ equivalent for MySQL’s phpMyAdmin, and both allow users to manage their databases from a web interface.

First, make sure phpPgAdmin is installed on your server. If it’s not, you might need to install it yourself, which requires root access.

With phpPgAdmin installed, you can log into its web interface and follow the steps below to export and download the data.

  • Locate the database you intend to export.
  • Click Export on the top menu bar.
  • For the export options, select SQL as the file format and click on Download.
  • Click on Export to run the task.
  • Select the location to save the file and click on Save.

Importing PostgreSQL Databases

The Postgres import feature allows users to restore backed-up files, add new files to a Postgres database or transfer a database to a new server.

You can run the import tasks in two ways. Let’s explore them.

Method 1: via The Command Line

You can import files to  Postgres via the command line using the psql program. Follow these steps to get started.

  • Here, we’re assuming you saved the file as dbbackup.pgsql.
  • To import this file, transfer it to your server using FTP, SCM or any file transfer protocol.
  • Open the psql command line and run this command.

psql -U username dbname < dbbackup.pgsql

Replace the username and dbname placeholders with your username and the database name you want to import the file into, respectively.

Method 2: via phpPgAdmin

Importing files via phpPgAdmin makes it much more manageable. Follow these steps to get started.

  • Log in to your phpPgAdmin web console.
  • Locate the database you intend to import files into and click on it.
  • Click on SQL on the top menu bar.
  • Click Choose File and select the file you want to import.
  • Click Execute to run the task.

Was this helpful?

What’s your goal today?

1. Sign up to our newsletter and keep up to date with the latest news. Our team consist of highly skilled ex-sysadmins and hosting veterans, ready to share their unique hosting-related knowledge with you.

Sign up for ScalaHosting monthly News Letter and we will keep you up to date about the most valuable pieces of content we publish on our blog

2. Explore our VPS Hosting - with our 30-day unconditional money-back guarantee and free, effortless migration, you can try our award-winning Managed Cloud VPS solutions risk-free.

Rado
Author

Working in the web hosting industry for over 13 years, Rado has inevitably got some insight into the industry. A digital marketer by education, Rado is always putting himself in the client's shoes, trying to see what's best for THEM first. A man of the fine detail, you can often find him spending 10+ minutes wondering over a missing comma or slightly skewed design.

Write a Comment

Required*

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.