How To Import and Export PostgreSQL Databases

Regularly backing up your database could save you a lot of headaches. It prevents tragic data loss and streamlines data archiving.

Of course, these require knowing how to export and import databases.

In this article, we explore the quick ways to run these tasks. The article guides you on everything you’d need to know to import and export PostgreSQL databases.

So, let’s get started.

Exporting PostgreSQL Databases

Exporting your Postgres database helps you download your data for safe-keeping or transfer 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.

If the file sits on a remote computer after the export, connect remotely to the server using FTP to download it to your local computer.

It’s pretty straightforward to execute this command, but if you run into issues while on it,  you could contact Scalahosting’s technical support. The team helps with database issues for free.

Method 2: via phpPgAdmin

Managing Postgres databases via the phpPgAdmin is much easier since you won’t need to memorize command syntax.

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

To export database via phpPgAdmin. You’d need to install and configure the phpPgAdmin console first. It could be trickier, but follow these steps to get it done.

If Apache and PHP are not running on your environment, install them from yum.

Download the current stable version of phpPgAdmin and move it to your server. Or, you could use wget to download the file directly to your server. And ensure your Postgres database is running on the same server.

Untar the phpPgAdmin file and move it to Apache DocumentRoot without the version number as shown below.

tar xvfz phpPgAdmin-7.1.tar.gz 

mv phpPgAdmin-7.1 /var/www/html/phpPgAdmin

Install the php-pgsql package from yum as shown here yum install php-pgsql

Modify the PostgreSQL pg_hba.conf file and add the following line to call the phpPgAdmin web console URL using your server’s internal IP address.

# vi /var/lib/pgsql/data/pg_hba.conf

host    all         all         192.168.101.2/32          md5

Using “Md5” allows you to log in to the phpPgAdmin web console using a valid username and password. It might prompt you to give the password when starting PostgreSQL.

You could replace “md5” with “trust” if you don’t want to log in with a password.

Locate config.inc.php, the phpPgAdmin’s config file, and set the extra_login_security parameter to false.

How To Import and Export PostgreSQL Databases

Setting this to false will allow you to log in from the phpPgAdmin web console using these usernames: 

  • Root
  •  Postgres
  • Pgsql
  • administrator

Also, set this parameter $conf[‘servers’][0][‘host’] to localhost or the IP address you’ll be using to call the phpPgAdmin web console.

Configure SELinux for phpPgAdmin. Use restorecon to retore SELinux context to the correct default. 

# restorecon -R phpPgAdmin/

Set httpd_can_network_connect_db to 1 using thr setsebool command.

Setsebool -P httpd_can_network_connect_db 1

Login to your phpPgAdmin web console using your IP address, for example, http://ip-address/phpPgAdmin/

That’s it.

If you run into any issue while setting up the web console, kindly contact the Support team.

While logged in to the web console, follow these steps to export a database.

  • 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 entirely 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.

Final Words

Backing up database files helps prevent a tragic loss of data. 

Use the Postgres export feature to download the backup files and import them whenever you want to restore the file to a database or move to a new server.

Thankfully, this article provides actionable guides on how to run these tasks.

Of course, don’t hesitate to contact ScalaHosting whenever you run into an issue. As pointed out earlier, the Support team provides free help on database issues.

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*