![]() ![]() You can configure a cron job to automate PostgreSQL database backup as follows. Cron jobs are a commonly used means for scheduling various kinds of tasks to run on a server. You can perform backups at regular intervals using cron jobs. $ pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdbĪuto Backup PostgreSQL Database Using a Cron Job It is also possible to dump a database directly from one server to another, use the pg_dump and psql utilities as shown. $ pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sqlĮnsure 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. and tecmintdb with your remote host IP address or hostname, database port, and database name respectively. Besides, use the -U flag to specify the database role name to connect as. To specify the remote database server pg_dump should contact, use the command-line options -h to specify the remote host and -p specifies the remote port the database server is listening on. Pg_dump is a regular PostgreSQL client tool, it supports operations on remote database servers. 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. If the database is extremely large, you can dump in parallel by dumping number_of_jobs tables simultaneously using the -j flag, as shown. Here is an example of how to restore a plain text file dump: $ psql tecmintdb tecmintdb.gz psql is used to restore text files created by pg_dump whereas pg_restore is used to restore a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats (custom, tar, or directory). To restore a PostgreSQL database, you can use the psql or pg_restore utilities. You can restore the dump using psql as shown. To back up all PostgreSQL databases, use the pg_dumpall tool as shown. $ pg_dump -F d tecmintdb -f tecmintdumpdir The directory which will be created by pg_dump must not exist. 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. 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.įor example: $ pg_dump -F c tecmintdb > tecmintdb.dump The pg_dump supports other output formats as well. ![]() By default, the output format is a plain-text SQL script file. If you did not understand the preceding paragraph then read the next section.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 name of the database you want to backup). The bottom line is this: if you try to start an application program and it complains that it cannot connect to the database, you should consult your site administrator or, if that is you, the documentation to make sure that your environment is properly set up. The environment variable PGPORT might also have to be set. For example, if the database server machine is a remote machine, you will need to set the PGHOST environment variable to the name of the database server machine. If your site administrator has not set things up in the default way, you might have some more work to do. Be sure to follow closely the section about setting up the appropriate environment variables. ![]() If you are installing PostgreSQL yourself, then refer to Chapter 17 for instructions on installation, and return to this guide when the installation is complete. PostgreSQL can be installed by any unprivileged user no superuser ( root) access is required. Doing so is not hard and it can be a good exercise. If you are not sure whether PostgreSQL is already available or whether you can use it for your experimentation then you can install it yourself. If that is the case, you should obtain information from the operating system documentation or your system administrator about how to access PostgreSQL. It is possible that PostgreSQL is already installed at your site, either because it was included in your operating system distribution or because the system administrator already installed it. Before you can use PostgreSQL you need to install it, of course. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |