administration

PostgreSQL Tutorial: Enable WAL Archiving

Summary: in this tutorial, you will learn how to enable WAL archiving in PostgreSQL.

Continue reading

PostgreSQL Tutorial: Setting up Logical Replication

Summary: Built-in logical replication and logical decoding were introduced in PostgreSQL 10. Over a period of time, more and more features are being implemented within logical replication. It is helpful when a selected list of tables needs to be replicated from multiple OLTP databases to a single OLAP database for reporting and analysis.

Continue reading

PostgreSQL Tutorial: Setting up Streaming Replication

Summary: Streaming replication (physical replication), which is byte-by-byte replication, involves a continuous application of WAL records from Primary to Standby. As it uses a file-based log shipping method, it is one of the fastest replication methods when compared with logical replication or other trigger-based methods.

Continue reading

PostgreSQL Tutorial: Backing up a database cluster using pg_basebackup

Summary: pg_basebackup is the built-in binary format backup tool available with PostgreSQL. Only the backups taken using pg_basebackup can be used for point-in-time recovery and not pg_dump or pg_dumpall.

Continue reading

PostgreSQL Tutorial: Restoring a backup taken using pg_basebackup

Summary: In the backing up a database cluster tutorial, we discussed how pg_basebackup can be used to take a physical backup of the PostgreSQL cluster. We may restore this backup in various situations, such as creating a standby server in replication or recovering a database to a point in time. In this tutorial, we shall see the steps required to restore a backup taken using pg_basebackup.

Continue reading

PostgreSQL Tutorial: Minor Version Update

Summary: Every 3 months, the PostgreSQL community releases minor version updates that are available for all the supported PostgreSQL versions. Let’s say that we are using PostgreSQL 13.1 but a new minor version, PostgreSQL 13.2, has been released. In this recipe, we shall see how a minor version upgrade can be performed when using PostgreSQL 13.

Continue reading

PostgreSQL Tutorial: PostgreSQL Administration

The PostgreSQL administration covers the most important PostgreSQL database server administration activities.

Continue reading

PostgreSQL Tutorial: PostgreSQL Server and Database Objects

Summary: in this tutorial, you are going to get familiar with the most common server and database objects provided by PostgreSQL. It is important to understand those objects and their functionality so you do not miss out on the cool features that you may wish to have in the system.

Continue reading

PostgreSQL Tutorial: Change The Password for a User

Summary: in this tutorial, you will learn how to change the password for a user in PostgreSQL.

Continue reading

PostgreSQL Tutorial: Copy Table

Summary: in this tutorial, we will show you step by step how to copy an existing table including table structure and data by using the various forms of PostgreSQL copy table statement.

Continue reading