Introduction
As an admin to a server, you’ve got to utilize every tool at your fingertips to maximize your efficiency to help you manage more servers at ease.
These utilities can help administrators to create a backup, locally or remotely.
PostgreSQL is an open-source relational database system that is free and widely used. The system offers a wide array of tools to accomplish tasks quickly and efficiently without any hassle.
This article will help you understand how you can use these processes and command to help you in your work
These commands will help your up-and-running servers that are Linux-based and have PostgreSQL installed in them. A root password setup is also required on the server itself, so before you can use these codes so make sure the site is already set up with these.
The pg_dump command
Backing up databases can be a hassle if you don't have the right tools in mind to use. The pg_dump command extracts a PostgreSQL database and then turns it into a script file or an archive file. This utility command is used for backing up databases and the backups it makes are consistent even if the database is used minimally. While using pg_dump, allowing readers, writers, and other users to use the database without blocking them
Using this utility will only dump a single database, if you’re looking to dump multiple files check out the pg_dumpall command.
The output of pg_dump
Outputs are either script or archive files. the difference is, that a script dump is a plain text file that contains SQL commands that can reconstruct the database to the same state when saved. Inputting the file to psql will restore these scripts. The script files are versatile and usually work on other databases and can be used with different types of machines and architectures.
With a few tinkering in the options, it can even allow the file to be used with other SQL database products.
While archive formats are not that versatile and must only be used by pg_restore for the database to be restored. Though not as versatile as an output, the archive allows the user to pinpoint the items that are going to be restored and can even arrange the items before restoring them, which makes the archive format helpful for porting databases across architectures.
Using archiving format will also give flexibility when transferring information across multiple platforms. The format with pg_dump and restoring it with pg_restore gives you more accurate results when restoring. the whole database will be backed-up with pg_dump while pg_restore allows you to select which part of the database to restore specifically.
Formats like “custom” and “directory” allow output files to be more versatile, giving them support to reorder archived items and parallel restoration then automatically comes in compressed format but parallel dumps can only be run by using the “directory” format
PostgreSQL statistics collector
Any errors after you ran pg_dump can be seen in the output that gets printed.
When you run pg_dump, it will execute “select” statements allowing you to select information coming in from the database using psql allowing the operations to work orderly and smoothly, and when running the utility, front-end library variables like libpq will continue to run.
The statistics collector is in charge of collecting database activity for pg_dump. Alternatively, with the command "alter user" or by going to "pgoptions" you can change the parameter track_counts.
Command structure
A standard command follows this format:
If you’re still confused, PostgreSQL has documentation that covers all of the options available for pg_dump this includes how you can write and what does the option do
A pg_dump example:
Dumping a database called scatterdb into an SQL file:
Backing up the scatterdb in with a tar format:
Saving the scatterdb in a directory format runs this command:
Large databases that want smaller file formats may use the utility with a compression tool like gzip when running the backup:
Reloading a script into a newly created database called nowdb:
Dumping a single table is possible by using the -t option and specifying the database and tab name (here: scatterdb; tallytab)
Dumping selected tables by putting in conditions. Here the command will dump all tables that start with "sam" in the "ple" schema, but will exclude the table "goliath."
The pg_restore command
The pg_restore command takes an archive file created by a pg_dump command and restores the selected PostgreSQL database.
When pg_dump is used with one of the non-plain text formats, the restore command will bring the database back. The utility knows how to issue commands in the proper order to make sure the database is reconstructed to the exact state it was in when the save occurred.
Since this format is supposed to be portable across architectures, the user may select what is restored and in what order.
The options for what can be done with the data depending on how the source file was generated. The command can't regenerate data that isn't there, and it can't change the nature of commands used to create the file.
pg_restore modes
pg_restore has two modes to utilize, first if a user specifies a database name, pg_restore will connect to that database and it will directly restore content on it but if the name has been left out, the process will create a script using SQL commands to rebuild the database.
Alternatively, the result is a standard output file and it will look the same as the standard text script pg_dump generates.
Command structure and format
The command will follow this format:
If you’re looking to expand your knowledge about PostgreSQL or want to see the full list of options for these commands, check out the PostgreSQL documentation.
See a pg_restore example
A backup .tar file name that the user wants to restore will require the user to choose what would the format of the backup would be and what the format of the backup is.
For example:
If the database dbfremen is already present, the following command will restore it:
While if the database doesn't yet exist, the command can be tweaked and will restore it like this:
these commands will restore a backup from a backup file i.e. name: back_it_on_up.sql
Summary of the pg_dumpall command
The PostgreSQL utility extracts a database cluster into a script file
Using pg_dumpall, one command allows the user to back up an entire cluster of databases and dump them out into one script file. The file works the same as the pg_dump command, meaning that the script will use SQL commands to restore all databases.
In fact, this command will call pg_dump for each database in the cluster. Some parts of the architecture, like global objects, are saved too. Database roles, tablespaces, and any information that is common to all databases will be saved by pg_dumpall, something that pg_dump will never touch.
Using pg_dumpall
To effectively use the tool, you will likely have to be logged into the database as a superuser to get a complete dump. Superuser privileges will also be useful to execute the saved script so that you can add roles and create databases.
The final file will use the standard SQL script output. Running this utility will require connecting to the PostgreSQL server once per database while performing the dump. If you use password authentication, you will have to provide the password for each database in the cluster.
Error messages
Many of the error messages that pop up will refer to pg_dump because the command runs this utility internally. Some errors will inevitably come up, but won't mean anything. The script will "create roles" for every role existing in the cluster you are using.
Roles like the bootstrap superuser, will likely get an error that says, "role already exists."
Using the --clean option
Databases will retain any previous contents and database-level properties. If you want to be sure that the databases are restored exactly as they are, using the --clean option may be useful.
The option authorized the script to recreate the built-in databases and makes sure that each database will have the same properties they had previously in the cluster. Using this option will kick back some errors about non-existent objects, but these errors can be ignored.
Using the --if-exists option
--if-exists can help you remove and declutter distracting errors by just simply adding it
Running the command “analyze” on every database will give the optimizer useful statistics to determine if the restore went well.
Command structure and format
The command for the pg_dumpall utility will be structured like the following:
If you are looking for something specific, PostgreSQL has extensive documentation that covers all the options available for using the tool.
See a pg_dumpall example
The following command will dump all databases:
This command will reload databases from the file:
This command will dump all files and create a single file called back_it_on_up.sql
Conclusion
With little to no effort, these utilities that are featured in PostgreSQL can be utilized and will help you finish your job swiftly and accurately giving you more time to focus on other tasks.
If the user wants to dump or restore a file and back up everything or just wants to back up a single database, depends on the command they will employ. Backing up can be customized depending on your preference and there are many options for it and it will define what will be the end result.
Automated PostgreSQL Backups service
If you want to make organizing and managing your databases easier, you can use automated PostgreSQL backup services online for example Ottomatik
Ottomatik is a database and website backup automation tool that offloads all the backups tasks so you can work efficiently and effortlessly.
Ottomatik automates PostgreSQL backups and instantly sends them to your cloud storage with ease
Not only does Ottomatik help you ensure that your backups are well-maintained and properly secured, but it also offers more tools to help you do your tasks and give you the peace of mind to run servers efficiently saving you the hassle of manually managing databases. Check out Ottomatik to learn more on how it can make your workflow easier and smoother.
