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.
Setting this to false will allow you to log in from the phpPgAdmin web console using these usernames:
Also, set this parameter $conf[‘servers’][‘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/
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.
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.