What PostgreSQL command-line utility backs up PostgreSQL database settings
By default, Xpress Insight uses a PostgreSQL 9.5 database server included with the distribution. Show
In addition to understanding the notes in this section, for enterprise-class deployments of Xpress Insight, system administrators should be proficient in the use of the PostgreSQL command line tools for the backup and restoration of PostgreSQL database. Additionally, an enterprise-class backup system should include a backup rotation policy. Such further guidance is outside the scope of this document.
Extensive options for PostgreSQL backup are described in the online documentation for the database: https://www.postgresql.org/docs/9.5/static/backup-dump.html. The following steps describe the actions necessary to implement backup and restore using the pg_dump and pg_restore utilitity tools provided. FICO is unable to provide support for alternative methods of backing up and restoring the PostgreSQL database. The PostgreSQL server is an open source robust and highly-extensible database server based on the SQL language. This part describes how to install and configure PostgreSQL on a RHEL system, how to back up PostgreSQL data, and how to migrate from an earlier PostgreSQL version 4.1. Getting started with PostgreSQLThe PostgreSQL server provides an object-relational database system, which allows you to manage extensive datasets and a high number of concurrent users. For these reasons, the PostgreSQL servers can be used in clusters to manage high amounts of data. The PostgreSQL server includes features for ensuring data integrity, building fault-tolerant environments, and building applications. It allows users to extend a database with users' own data types, custom functions, or code from different programming languages without the need to recompile the database. This part describes:
4.2. Installing PostgreSQLRHEL 9.0 provides PostgreSQL 13 as the initial version of this Application Stream, which can be installed easily as an RPM package. Additional PostgreSQL versions will be provided as modules with a shorter life cycle in the future minor releases of RHEL 9. To install PostgreSQL, use the following procedure. Procedure
4.3. Creating PostgreSQL usersPostgreSQL users are of the following types:
Roles can own database objects (for example, tables and functions) and can assign object privileges to other roles using SQL commands. Standard database management privileges include Role attributes are special privileges, such as
Red Hat recommends performing most tasks as a role that is not a superuser. A common practice is to create a role that has the Prerequisites
Procedure
Example 4.1. Initializing, creating, and connecting to a PostgreSQL database This example demonstrates how to initialize a PostgreSQL database, create a database user with routine database management privileges, and how to create a database that is accessible from any system account through the database user with management privileges.
4.4. Configuring PostgreSQL In a PostgreSQL database, all data and configuration files are stored in a single directory called a database cluster. Red Hat recommends storing all data, including configuration files, in the default PostgreSQL configuration consists of the following files:
To change the PostgreSQL configuration, use the following procedure. Procedure
Example 4.2. Configuring PostgreSQL database cluster parameters This example shows basic
settings of the database cluster parameters in the # This is a comment log_connections = yes log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB password_encryption = scram-sha-256 Example 4.3. Setting client authentication in PostgreSQL This example demonstrates how to set client authentication in the # TYPE DATABASE USER ADDRESS METHOD local all all trust host postgres all 192.168.93.0/24 ident host all all .example.com scram-sha-256 4.5. Configuring TLS encryption on a PostgreSQL server
By default, PostgreSQL uses unencrypted connections. For more secure connections, you can enable Transport Layer Security (TLS) support on the PostgreSQL server and configure your clients to establish encrypted connections. Prerequisites
Procedure
Verification
Example 4.4. Initializing, creating, and connecting to a PostgreSQL database using TLS encryption This example demonstrates how to initialize a PostgreSQL database, create a database user and a database, and how to connect to the database using a secured connection.
4.6. Backing up PostgreSQL dataTo back up PostgreSQL data, use one of the following approaches: 4.6.1. Backing up PostgreSQL data with an SQL dumpThe SQL dump method is based on generating a dump file with SQL commands. When a dump is uploaded back to the database server, it recreates the database in the same state as it was at the time of the dump. The SQL dump is ensured by the following PostgreSQL client applications:
By default, the You can perform the SQL dump from any remote host that has access to the database. 4.6.1.1. Advantages and disadvantages of an SQL dumpAn SQL dump has the following advantages compared to other PostgreSQL backup methods:
A disadvantage of an SQL dump is that it takes more time compared to file system level backup. 4.6.1.2. Performing an SQL dump using pg_dumpTo dump a single database without cluster-wide information, use the pg_dump utility. Prerequisites
Procedure
To specify which database server pg_dump will contact, use the following command-line options:
4.6.1.3. Performing an SQL dump using pg_dumpallTo dump each database in a given database cluster and to preserve cluster-wide data, use the pg_dumpall utility. Prerequisites
Procedure
To specify which database server pg_dumpall will contact, use the following command-line options:
4.6.1.4. Restoring a database dumped using pg_dumpTo restore a database from an SQL dump that you dumped using the pg_dump utility, follow the steps below. Prerequisites
Procedure
4.6.1.5. Restoring databases dumped using pg_dumpallTo restore data from a database cluster that you dumped using the pg_dumpall utility, follow the steps below. Prerequisites
Procedure
4.6.1.6. Performing an SQL dump of a database on another serverDumping a database directly from one server to another is possible because pg_dump and psql can write to and read from pipes. Procedure
4.6.1.7. Handling SQL errors during restoreBy default, psql continues to execute if an SQL error occurs, causing the database to restore only partially. To change the default behavior, use one of the following approaches when restoring a dump. Prerequisites
Procedure
4.6.1.8. Additional resources
4.6.2. Backing up PostgreSQL data with a file system level backupTo create a file system level backup, copy PostgreSQL database files to another location. For example, you can use any of the following approaches:
4.6.2.1. Advantages and limitations of file system backing upFile system level backing up has the following advantage compared to other PostgreSQL backup methods:
File system level backing up has the following limitations compared to other PostgreSQL backup methods:
4.6.2.2. Performing file system level backing upTo perform file system level backing up, use the following procedure. Procedure
4.6.3. Backing up PostgreSQL data by continuous archiving4.6.3.1. Introduction to continuous archiving PostgreSQL records every change made to the database’s data files into a write ahead log (WAL) file that is available in the The continuous archiving method, also known as an online backup, combines the WAL files with a copy of the database cluster in the form of a base backup performed on a running server or a file system level backup. If a database recovery is needed, you can restore the database from the copy of the database cluster and then replay log from the backed up WAL files to bring the system to the current state. With the continuous archiving method, you must keep a continuous sequence of all archived WAL files that extends at minimum back to the start time of your last base backup. Therefore the ideal frequency of base backups depends on:
You cannot use pg_dump and pg_dumpall SQL dumps as a part of a continuous archiving backup solution. SQL dumps produce logical backups and do not contain enough information to be used by a WAL replay. To perform a database backup and restore using the continuous archiving method, follow these instructions:
To restore your data, follow instructions in Restoring database with continuous archiving. 4.6.3.2. Advantages and disadvantages of continuous archivingContinuous archiving has the following advantages compared to other PostgreSQL backup methods:
Continuous archiving has the following disadvantages compared to other PostgreSQL backup methods:
4.6.3.3. Setting up WAL archivingA running PostgreSQL server produces a sequence of write ahead log (WAL) records. The server physically divides this sequence into WAL segment files, which are given numeric names that reflect their position in the WAL sequence. Without WAL archiving, the segment files are reused and renamed to higher segment numbers. When archiving WAL data, the contents of each segment file are captured and saved at a new location before the segment file is reused. You have multiple options where to save the content, such as an NFS-mounted directory on another machine, a tape drive, or a CD. Note that WAL records do not include changes to configuration files. To enable WAL archiving, use the following procedure. Procedure
The archive command is executed only on completed WAL segments. A server that generates little WAL traffic can have a substantial delay between the completion of a transaction and its safe recording in archive storage. To limit how old unarchived data can be, you can:
Example 4.5. Shell command for archiving WAL segments This example shows a
simple shell command you can set in the The following command copies a completed segment file to the required location: archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' where the This command copies archivable WAL segments to the test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_wal/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065
A similar command is generated for each new file that is archived. 4.6.3.4. Making a base backupYou can create a base backup in several ways. This section describes the simplest way of performing a base backup using the pg_basebackup utility on a running PostgreSQL server. The base backup process creates a backup history file that is stored into the WAL archive area and is named after the first WAL segment file that you need for the base backup. The backup history file is a small text file containing the starting and ending times, and WAL segments of the backup. If you used the label string to identify the associated dump file, you can use the backup history file to determine which dump file to restore. Consider keeping several backup sets to be certain that you can recover your data. To perform a base backup, use the following procedure. Prerequisites
Procedure
To specify which database server pg_basebackup will contact, use the following command-line options:
4.6.3.5. Restoring the database using a continuous archive backupTo restore a database using a continuous backup, use the following procedure. Procedure
For more information about restoring using the continuous backup, see PostgreSQL Documentation. 4.6.3.6. Additional resources
4.7. Migrating to a RHEL 9 version of PostgreSQLRed Hat Enterprise Linux 8 provides PostgreSQL in multiple module streams: PostgreSQL 10 (the default postgresql stream), PostgreSQL 9.6, PostgreSQL 12, and PostgreSQL 13. In RHEL 9, PostgreSQL 13 is available. On RHEL, you can use two PostgreSQL migration paths for the database files:
The fast upgrade method is quicker than the dump and restore process. However, in certain cases, the fast upgrade does not work, and you can only use the dump and restore process, for example in case of cross-architecture upgrades. As a prerequisite for migration to a later version of PostgreSQL, back up all your PostgreSQL databases. Dumping the databases and performing backup of the SQL files is required for the dump and restore process and recommended for the fast upgrade method. Before migrating to a later version of PostgreSQL, see the upstream compatibility notes for the version of PostgreSQL to which you want to migrate, as well as for all skipped PostgreSQL versions between the one you are migrating from and the target version. 4.7.1. Fast upgrade using the pg_upgrade utility As an system administrator, you can upgrade to the most recent version of PostgreSQL by using the fast upgrade method. To perform a fast upgrade, you must copy binary data files to the The following procedure describes migration from the RHEL 8 version of PostgreSQL 12 to the RHEL 9 version of
PostgreSQL 13 using the fast upgrade method. For migration from
Prerequisites
Procedure
4.7.2. Dump and restore upgradeWhen using the dump and restore upgrade, you must dump all databases contents into an SQL file dump file. Note that the dump and restore upgrade is slower than the fast upgrade method and it may require some manual fixing in the generated SQL file. You can use this method for migrating data from any RHEL 8 version of PostgreSQL to the RHEL 9 version of PostgreSQL 13. On RHEL 8 and RHEL 9 systems, PostgreSQL data is stored in the To perform the dump and restore upgrade, change the user to The following procedure describes migration from the RHEL 8 default version of Postgreql 10 to the RHEL 9 version of PostgreSQL 13. Procedure
What is PostgreSQL command line tools?psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments.
What are the different approaches to backing up PostgreSQL data?There are three fundamentally different approaches to backing up PostgreSQL data: SQL dump. File system level backup. Continuous archiving.
Which command is used to the list of databases in PostgreSQL?To view all of the defined databases on the server you can use the \list meta-command or its shortcut \l .
What is the difference between pg_dump and Pg_dumpall?One caveat: pg_dump does not dump roles or other database objects including tablespaces, only a single database. To take backups on your entire PostgreSQL cluster, pg_dumpall is the better choice. pg_dumpall can handle the entire cluster, backing up information on roles, tablespaces, users, permissions, etc…
|