Java Postgres Drop Old Database and Start Again
In a production environment, no matter how big or small-scale your PostgreSQL database may be, regular dorsum is an essential aspect of database direction. In this commodity, you volition learn how to fill-in and restore a PostgreSQL database.
We presume that you already have a working installation of the PostgreSQL database organisation. If not, read our following manufactures to install PostgreSQL on your Linux distribution.
- How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04
- How to Install PostgreSQL and pgAdmin in CentOS 8
- How to Install PostgreSQL and pgAdmin in RHEL viii
Let's go started…
Backup a Unmarried PostgreSQL Database
PostgreSQL provides the pg_dump utility to help you back up databases. It generates a database file with SQL commands in a format that can exist hands restored in the future.
To back up, a PostgreSQL database, start by logging into your database server, then switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the proper noun of the database you want to backup). By default, the output format is a plain-text SQL script file.
$ pg_dump tecmintdb > tecmintdb.sql
The pg_dump supports other output formats as well. You tin specify the output format using the -F option, where c ways custom format annal file, d ways directory format archive, and t means tar format archive file: all formats are suitable for input into pg_restore.
For case:
$ pg_dump -F c tecmintdb > tecmintdb.dump OR $ pg_dump -F t tecmintdb > tecmintdb.tar
To dump output in the directory output format, use the -f flag (which is used to specify the output file) to specify the target directory instead of a file. The directory which volition be created by pg_dump must not exist.
$ pg_dump -F d tecmintdb -f tecmintdumpdir
To dorsum upward all PostgreSQL databases, use the pg_dumpall tool as shown.
$ pg_dumpall > all_pg_dbs.sql
You lot tin 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, you can apply 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 past pg_dump in one of the non-plain-text formats (custom, tar, or directory).
Here is an example of how to restore a plain text file dump:
$ psql tecmintdb < tecmintdb.sql
Equally mentioned to a higher place, a custom-format dump is not a script forpgsql, then information technology must exist restored withpg_restore as 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 backing upwards is large and you lot want to generate a adequately smaller output file, then you lot can run a compressed dump where you have to filter the output of pg_dump via a pinch tool such as gzip or whatsoever of your favorite:
$ pg_dump tecmintdb | gzip > tecmintdb.gz
If the database is extremely large, you can dump in parallel past dumping number_of_jobs tables simultaneously using the -j flag, equally shown.
$ pg_dump -F d -j v -f tecmintdumpdir
It is important to note that the parallel dump choice reduces the time of the dump, but on the other mitt, it too increases the load on the database server.
Fill-in Remote PostgreSQL Databases
pg_dump is a regular PostgreSQL client tool, information technology supports operations on remote database servers. To specify the remote database serverpg_dump should contact, use the control-line options -h to specify the remote host and-p specifies the remote port the database server is listening on. Besides, use the -U flag to specify the database office name to connect as.
Call up to replace 10.10.20.x and 5432 and tecmintdb with your remote host IP address or hostname, database port, and database name respectively.
$ pg_dump -U tecmint -h ten.10.xx.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 lot volition get an error like the one shown in the post-obit screenshot.
It is also possible to dump a database directly from one server to another, utilise the pg_dump and psql utilities as shown.
$ pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h ten.10.20.xxx tecmintdb
Motorcar Fill-in PostgreSQL Database Using a Cron Job
You 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 can configure a cron job to automate PostgreSQL database backup as follows. Note that yous need to run the post-obit commands equally the PostgreSQL superuser:
$ mkdir -p /srv/backups/databases
Next, run the following command to edit the crontab to add a new cron chore.
$ crontab -eastward
Copy and paste the following line at the end of the crontab. You can utilize any of the dump formats explained higher up.
0 0 * * * pg_dump -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql
Save the file and exit.
The cron service will automatically start running this new chore without a restart. And this cron chore volition run every twenty-four hours at midnight, it is a minimum solution to the backup chore.
For more than data on how to schedule cron jobs, encounter: How to Create and Manage Cron Jobs on Linux
That'south it for at present! It'southward a practiced thought to make backing up data a part of your database direction routine. To accomplish us for any questions or comments, utilize the feedback form beneath. For more information, see the pg_dump and pg_restore reference pages.
If You Appreciate What We Practise Here On TecMint, Yous Should Consider:
TecMint is the fastest growing and most trusted customs site for any kind of Linux Articles, Guides and Books on the spider web. Millions of people visit TecMint! to search or scan the thousands of published articles available FREELY to all.
If you lot like what yous are reading, please consider buying us a coffee ( or two ) as a token of appreciation.
We are thankful for your never ending support.
Source: https://www.tecmint.com/backup-and-restore-postgresql-database/
0 Response to "Java Postgres Drop Old Database and Start Again"
Post a Comment