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.
