How To Install Pg_dump
In a production environment, no thing how large or small your PostgreSQL database may be, regular back is an essential attribute of database management. In this article, you lot will learn how to fill-in and restore a PostgreSQL database.
Nosotros assume that you already have a working installation of the PostgreSQL database organization. If not, read our following articles to install PostgreSQL on your Linux distribution.
- How to Install PostgreSQL and pgAdmin4 in Ubuntu xx.04
- How to Install PostgreSQL and pgAdmin in CentOS viii
- How to Install PostgreSQL and pgAdmin in RHEL 8
Allow'due south become started…
Fill-in a Unmarried PostgreSQL Database
PostgreSQL provides the pg_dump utility to help you lot back upward databases. It generates a database file with SQL commands in a format that can be easily restored in the future.
To back up, a PostgreSQL database, start past logging into your database server, so switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the name of the database you want to fill-in). By default, the output format is a plain-text SQL script file.
$ pg_dump tecmintdb > tecmintdb.sql
The pg_dump supports other output formats also. You can specify the output format using the -F option, where c means custom format archive file, d means directory format archive, and t means tar format archive file: all formats are suitable for input into pg_restore.
For instance:
$ pg_dump -F c tecmintdb > tecmintdb.dump OR $ pg_dump -F t tecmintdb > tecmintdb.tar
To dump output in the directory output format, utilise the -f flag (which is used to specify the output file) to specify the target directory instead of a file. The directory which will be created past pg_dump must not exist.
$ pg_dump -F d tecmintdb -f tecmintdumpdir
To dorsum up all PostgreSQL databases, use the pg_dumpall tool every bit shown.
$ pg_dumpall > all_pg_dbs.sql
You can restore the dump using psql every bit shown.
$ pgsql -f all_pg_dbs.sql postgres
Restoring a PostgreSQL Database
To restore a PostgreSQL database, y'all tin can use the psql or pg_restore utilities. psql is used to restore text files created bypg_dump whereas pg_restore is used to restore a PostgreSQL database from an archive created by pg_dump in one of the non-plainly-text formats (custom, tar, or directory).
Here is an example of how to restore a apparently text file dump:
$ psql tecmintdb < tecmintdb.sql
Equally mentioned above, a custom-format dump is not a script forpgsql, so it must exist restored withpg_restore equally shown.
$ pg_restore -d tecmintdb tecmintdb.dump OR $ pg_restore -d tecmintdb tecmintdb.tar OR $ pg_restore -d tecmintdb tecmintdumpdir
Backup Large PostgreSQL Databases
If the database you are bankroll up is big and you want to generate a adequately smaller output file, then you can run a compressed dump where you lot take to filter the output of pg_dump via a pinch tool such as gzip or any of your favorite:
$ pg_dump tecmintdb | gzip > tecmintdb.gz
If the database is extremely large, y'all can dump in parallel by dumping number_of_jobs tables simultaneously using the -j flag, as shown.
$ pg_dump -F d -j v -f tecmintdumpdir
It is important to note that the parallel dump option reduces the time of the dump, but on the other hand, it also increases the load on the database server.
Backup Remote PostgreSQL Databases
pg_dump is a regular PostgreSQL customer tool, it supports operations on remote database servers. To specify the remote database serverpg_dump should contact, apply the command-line options -h to specify the remote host and-p specifies the remote port the database server is listening on. Besides, employ the -U flag to specify the database role name to connect equally.
Think to replace ten.10.twenty.10 and 5432 and tecmintdb with your remote host IP accost or hostname, database port, and database name respectively.
$ pg_dump -U tecmint -h 10.10.twenty.10 -p 5432 tecmintdb > tecmintdb.sql
Ensure that the user connecting remotely has the required privileges to access the database, and the appropriate database authentication method is configured on the database server, otherwise, you will get an error like the one shown in the following screenshot.
It is too possible to dump a database directly from one server to some other, use the pg_dump and psql utilities as shown.
$ pg_dump -U tecmint -h x.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdb
Auto Fill-in PostgreSQL Database Using a Cron Job
Y'all can perform backups at regular intervals using cron jobs. Cron jobs are a commonly used means for scheduling various kinds of tasks to run on a server.
You tin can configure a cron job to automate PostgreSQL database backup as follows. Note that you demand to run the following commands as the PostgreSQL superuser:
$ mkdir -p /srv/backups/databases
Side by side, run the following command to edit the crontab to add a new cron task.
$ crontab -east
Re-create and paste the post-obit line at the stop of the crontab. Yous can utilise any of the dump formats explained above.
0 0 * * * pg_dump -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql
Salve the file and exit.
The cron service will automatically get-go running this new job without a restart. And this cron job will run every day at midnight, information technology is a minimum solution to the backup chore.
For more information on how to schedule cron jobs, see: How to Create and Manage Cron Jobs on Linux
That's it for now! It's a skillful idea to make backing upward data a part of your database direction routine. To achieve united states of america for whatever questions or comments, utilise the feedback form below. For more information, see the pg_dump and pg_restore reference pages.
If You Appreciate What Nosotros Practice Here On TecMint, You Should Consider:
TecMint is the fastest growing and nigh trusted community site for whatever kind of Linux Articles, Guides and Books on the spider web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.
If yous like what you are reading, delight consider ownership us a coffee ( or 2 ) as a token of appreciation.
We are thankful for your never ending back up.
Source: https://www.tecmint.com/backup-and-restore-postgresql-database/
Posted by: edwardspacts1965.blogspot.com
0 Response to "How To Install Pg_dump"
Post a Comment