Postgresql export database linux

How to Import and Export PostgreSQL Database

PostgreSQL is the modern and most popular database management system now a day. Backing up PostgreSQL data is one of the most important tasks for any database administrator. It is an essential component of a data storage plan. Backups will help you to recover your system in the event of database corruption. PostgreSQL provides pg_dump and a pg_dumpall command-line utility to effectively backup your important information.

When working on a different project in different environments, you often need to export a database dump from one server and import it into another server. So it is essential to implement a backup strategy to protect against data loss.

In this post, we will show you how to import and export a PostgreSQL database using pg_dump, pg_dumpall, psql, and pg_restore utility.

Export a PostgreSQL Database with pg_dump Command

The pg_dump is a command-line tool that allows you to dump the content of a specified database into a single file. The pg_dump command provides several options that help you to specify tables or schemas to back up.

The basic syntax of the pg_dump command is shown below:

• -h Specify database server host
• -p Specify database server port
• -U Specify the user which is used to connect to the PostgreSQL database server
• -W Used to prompt for a password before connecting to the PostgreSQL server
• -d Specify the database to dump
• -F Specify one of the following output format files:
• p plain-text format
• c custom format
• d directory format
• t tar format

To export a database named testdb into a plain-text format, run the following command:

 
pg_dump -U username -W -F p -d testdb > testdb.sql

To export a database named testdb into a tar format, run the following command:

 
pg_dump -U username -W -F t -d testdb > testdb.tar

To export a database named testdb into custom format, run the following command:

 
pg_dump -U username -W -F c -d testdb > testdb.dump

To export a database named testdb into a directory format, run the following command:

 
pg_dump -U username -W -F d -d testdb -f testdb-dir

If you want to export a large database and generate a smaller dump file, you can use the gzip tool with the pg_dump command to compress the dump file.

 
pg_dump -U username -W -F t -d testdb | gzip > testdb.tar.gz

Export All PostgreSQL Databases with pg_dumpall Command

The pg_dumpall is a command-line utility that allows you to export all PostgreSQL databases into a single file. This file contains several SQL commands that can be used as input to psql to restore the databases. The pg_dumpall reads tables from all databases, so you will have to connect to PostgreSQL as a superuser to produce a complete dump.

The basic syntax of the pgdump_all command is shown below:

 
pg_dumpall [option] > dump.sql

A brief explanation of each option is shown below:

• -h Specify database server host
• -p Specify database server port
• -c Clean databases before recreating
• -g Dump only global objects, no databases
• -r Dump only roles, no databases or tablespaces -s Dump only the schema, no data -S Specify superuser user name to use in the dump -t Dump only tablespaces definition.

To export all PostgreSQL databases, run the following command:

To back up objects in all databases, including roles, tablespaces, databases, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges, you use the following command:

If you want to back up role definition only, use the following command:

If you want to back up the tablespaces definition, use the following command:

Import a PostgreSQL Database with pg_restore Command

The pg_restore is a command-line utility used to import or restore a PostgreSQL database from an archive created by pg_dump. Generally, it is used if you choose custom, directory, or tar format when creating a dump file.

The pg_restore command provides a -j option to specify the number of threads for restoration. This will allow you to perform parallel restores and dramatically speed up the process. Currently, the pg_restore support this option for the only custom file format.

The basic syntax of the pg_restore command is shown below:

 
pg_restore -c -U db_user -W -F t -d db_name dump_file.tar

A brief explanation of each option is shown below:

• -U Specify the user who is used for connecting to the PostgreSQL database server.
• -d Specify the database on which you want to import or restore a database.
• -W Used to prompt for a password before connecting to the PostgreSQL server.
• -F format Specify the format of the archive.
• -j Specify the number of parallel jobs to restore.
• -c Clean a database before restoring them.
• -C Create a new database before restoring it.
• -e Exit the process if an error has been encountered.

For example, to import a single database named testdb from the dump file named dump.tar, run the following command:

 
pg_restore -c -U username -W -F t -d testdb dump.tar

If you want to import all databases from the alldump.tar file, run the following command:

 
pg_restore -c -U username -W -F t alldump.tar

Import a PostgreSQL Database with psql Command

The psql is a command-line tool that allows you to import the SQL dump file generated by the pg_dump or pg_dumpall.

The basic syntax to import a single database using the psql tool is shown below:

 
psql -U user_name -W -d db_name -f dump.sql

The basic syntax to import all databases using the psql tool is shown below:

 
psql -U user_name -W -f dump.sql

To import a single database from the dump file named dump.sql into the database named testdb, run the following command:

 
psql -U username -W -d testdb -f dump.sql

If you want to stop importing a database in case of errors, you add the —set ON_ERROR_STOP=on option:

 
psql -U username -W -d testdb --set ON_ERROR_STOP=on -f dump.sql

To import all PostgreSQL databases from the dump file named alldb_dump.sql, run the following command:

 
psql -U username -W -f alldb_dump.sql

Conclusion

In this guide, we explained how to export a PostgreSQL database using the pg_dump and pg_dumpall utility. We also explained how to import a dump file using the pg_restore and psql utility. I hope this will help you to perform your day-to-day backup jobs.

Thank you for helping us improve!

Источник

How to Import and Export a PostgreSQL Database

From time to time you may need to export or import a PostgreSQL database. For example, you might be moving the database from one hosting provider to another, importing a database from a third party for repair or migrations, or you may be taking a backup of the database.

This knowledgebase article covers the various ways to import and export a PostgreSQL database.

How to export a PostgreSQL database

Method #1 Use the pg_dump application

The pg_dump command will allow you to export a PostgreSQL database to a file. To export using this method, follow the steps below:

Step 1: Connect to your web hosting account via SSH. If you are not familiar with how to do this, click here to review our KB article on the topic.

Step 2: Enter the following command using your keyboard and at the end, press enter/return on your keyboard. Be sure to replace the ‘username’ with your cPanel default account username and replace ‘dbname’ with the name of the database you wish to export.

pg_dump -U username dbname > dbexport.pgsql

You will be prompted for your default cPanel user account password in order to complete the export.

Your database will be downloaded with the name dbexport.pgsql. You can change the name of the database to anything you want after saving the file, but don’t change the extension.

You may receive an error message, similar to the ones below, when running this command.

pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for schema topology pg_dump: The command was: LOCK TABLE topology.topology IN ACCESS SHARE MODE

These errors occur because some server database templates include PostGIS with restricted access permissions.

To export a PostgreSQL database without this data, type the following command instead of the command listed in step 2:

pg_dump -U username dbname -N topology -T spatial_ref_sys > dbexport.pgsql

Method #2 Use phpPGAdmin

Step 1: Login to your cPanel. There’s a lot of ways to do this, but the sure fire easiest way is to login to your Client Area, then open your cPanel.

Step 2: Scroll down to the Databases section and open phpPgAdmin.

Step 3: When phpPgAdmin opens, expand the Servers area from the left hand side, then expand PostgreSQL, and then click the name of the database you wish to export.

Step 4: From the menu at the top, click the Export button.

Under the Format column, select Structure and data. Under the Options column, in the Format list box, select SQL.

Then beneath Options, select Download.

The file will be saved to your computer.

Tired of experiencing issues with your site? Get the best and fastest hosting support with ChemiCloud! 🤓 Check out our web hosting plans!

How to import a PostgreSQL database

Before you can import a PostgreSQL database, you must create a new database in cPanel and assign a user to it.

You should import all of your PostgreSQL databases as the primary PostgreSQL user, aka the username assigned to your cPanel account. If you import PostgreSQL databases as a regular user, you will be unable to see or manipulate the data properly using phpPgAdmin.

After you have imported the data as the primary PostgreSQL user, you can grant a regular user access to the database. Then you won’t need to use the primary domain username and password in scripts that access the database.

Method # 1: Use the psql application

Step 1: Using SCP, SFTP, FTP, or the cPanel File Manager > Upload File tool, upload the database you want to import to your hosting account.

Step 2: Connect to your web hosting account via SSH. If you are not familiar with how to do this, click here to review our KB article on the topic.

Step 3: Enter the following command, then press enter/return on your keyboard. Replace ‘username’ with your cPanel username and replace ‘dbname’ with the name of the database you wish to import:

The ‘dbname’ database should now contain all the data that was in the database file you uploaded.

Method # 2: Use phpPgAdmin

To import a PostgreSQL database using phpPgAdmin, follow the steps below:

Step 1: Login to your cPanel. There’s a lot of ways to do this, but the sure fire easiest way is to login to your Client Area, then open your cPanel.

Step 2: Scroll down to the Databases section and open phpPgAdmin.

Step 3: From the left side of phpPgAdmin, expand the Servers area, then expand PostgreSQL, then click the name of the database you want to import data into.

Next click SQL from the row of options at the top of the page.

A white box will appear. You are able to enter text into this box, but you won’t need to. Instead, click the “Choose File” button beneath the text box.

When the file picker opens, choose the .postgresql file you wish to upload.

Then click Execute to instruct phpPgAdmin to import the data in that file into the database.

Additional Information

The official documentation for PostgreSQL can be very handy sometimes.

Find official PostgreSQL documentation here.

Источник