Forem: Caleb Mucheru

Postgres Data Backup with Continuous Archiving and Point-in-time-recovery

Caleb Mucheru — Fri, 02 Feb 2024 12:14:07 +0000

This blog is an alternative method to the SQL dump method for Postgresql data backup. This method has some advantages over methods for instance it does not need a perfectly consistent file system backup as the starting point.
Understanding Continuous Archiving
Continuous Archiving is a feature in PostgreSQL that enables the continuous streaming of transaction log files (WAL files) to an archive directory. This ensures that a complete history of changes to the database is maintained, allowing for point-in-time recovery.
Steps to Set Up Continuous Archiving
1.Configure PostgreSQL.conf
Modify the PostgreSQL configuration file (postgresql.conf) to enable continuous archiving. Set the wal_level to replica and define the archive_mode and archive_command parameters.

wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

2.Create Archive Directory
Create the specified archive directory and ensure that the PostgreSQL user has the necessary permissions to write to it.

mkdir /path/to/archive
chown postgres:postgres /path/to/archive

3.Restart PostgreSQL
Restart the PostgreSQL service to apply the changes made in the configuration file.

systemctl restart postgresql

Implementing Point-in-time Recovery (PITR)
Point-in-time Recovery allows you to restore your database to any specific point in time by replaying the transaction logs. This is invaluable in scenarios where you need to recover from a critical error or data corruption.

Steps for Point-in-time Recovery
1.Create a Base Backup
Initiate a base backup of the PostgreSQL cluster, which serves as the starting point for the recovery process.

pg_basebackup -D /path/to/backup

2.Restore the Base Backup
To perform a point-in-time recovery, restore the base backup to a new location.

pg_ctl stop -D /path/to/restore
rm -rf /path/to/restore/*
cp -r /path/to/backup/* /path/to/restore

3.Apply Transaction Logs
Copy the necessary archived WAL files to the new PostgreSQL cluster and start the server in recovery mode.

cp /path/to/archive/* /path/to/restore/pg_wal
pg_ctl -D /path/to/restore -l /path/to/restore/logfile start

4.Point-in-time Recovery
Use the pg_wal_replay_resume function to specify the desired recovery target time.

psql -c "SELECT pg_wal_replay_resume('target_time')"

Conclusion:
By implementing Continuous Archiving and Point-in-time Recovery in PostgreSQL, you can enhance the resilience of your data management system. Regularly creating base backups and archiving transaction logs ensures that you have a reliable backup strategy in place, minimizing the risk of data loss and allowing for precise recovery to any desired point in time.

Simplified Guide to PostgreSQL Data Backup with pg_dump and pg_dumpall

Caleb Mucheru — Wed, 24 Jan 2024 10:28:07 +0000

Introduction
In the PostgreSQL ecosystem, ensuring the safety and integrity of your data is paramount. The SQL dump method, facilitated by tools like pg_dump and pg_dumpall, allows you to generate files with SQL commands to recreate databases. Let's explore these tools and their usage for effective data backup.

Key Tools
pg_dump
Used for backing up individual databases.

Basic Usage:
```
 pg_dump dbname > dumpfile
```
Writes the result to standard output, creating a text file.
Operates remotely, requiring read access to tables (superuser for entire database).
Allows specification of host, port, and user for remote connections.
Advantage: Output generally reloadable into newer PostgreSQL versions.
Internally consistent dumps; does not block other operations.

pg_dumpall
Backs up entire database clusters, including roles and tablespaces.

Basic Usage:
```
 pg_dumpall > dumpfile
```
Resulting dump can be restored using psql.
Requires database superuser access for full restore.
Preserves cluster-wide data, but snapshots of different databases are not synchronized.

Restoring Backups
Using psql

  psql dbname < dumpfile

Requires manual creation of the database from template0 before execution.
Users who own objects or were granted permissions must exist before restoring.
Option to run psql with --set ON_ERROR_STOP=on for better error handling.
Specify --single-transaction for a fully completed or fully rolled-back restore.

Handling Large Databases
Compressed Dumps

  pg_dump dbname | gzip > filename.gz
  gunzip -c filename.gz | psql dbname

Split Output Files

  pg_dump dbname | split -b 2G - filename
  cat filename* | psql dbname

Custom Dump Format

  pg_dump -Fc dbname > filename
  pg_restore -d dbname filename

Parallel Dump

  pg_dump -j num -F d -f out.dir dbname
  pg_restore -j to restore dump in parallel.

Parallel dumps for the "directory" archive format.

An introduction to Postgres 🐘 Prepared Statements

Caleb Mucheru — Mon, 15 Jan 2024 14:16:00 +0000

In the realm of database management, optimizing query performance is a crucial aspect of ensuring the efficiency of applications. Postgres, a robust open-source relational database, offers a powerful feature known as "Prepared Statements" to enhance the execution of SQL queries. In this blog post, we'll delve into the concept of Postgres Prepared Statements, exploring their application in tables and composite types, providing sample SQL code, and elucidating the syntax for creating these statements. Additionally, we'll highlight the merits of leveraging Prepared Statements in your database interactions.
The syntax for creating Prepared Statements in Postgres involves two primary commands: PREPARE and EXECUTE.

PREPARE statement_name [ (data_type [, ...]) ] AS
    SQL_query_string;

EXECUTE statement_name [ (parameter [, ...]) ];

Let us explore the concept with both tables and composite types.
(a) In Tables
Prepared Statements in Postgres allow for the precompilation and reuse of SQL queries. In the context of tables, this translates to improved performance by reducing the overhead of parsing and planning queries for repetitive executions. Prepared Statements can be especially beneficial when dealing with frequently executed queries involving tables.

Example 1: Prepared Statement for Selecting Data from a Table

PREPARE select_employee (INT) AS
    SELECT * FROM employees WHERE employee_id = $1;

EXECUTE select_employee(1001);

(b) In Composite Types
Beyond tables, Prepared Statements extend their utility to composite types. Composite types in Postgres enable the grouping of related fields into a single user-defined type. Leveraging Prepared Statements in conjunction with composite types enhances code modularity and readability, making it easier to manage complex data structures.

Example 1: Prepared Statement for Selecting Data from a Table

CREATE TYPE person_type AS (
    name VARCHAR,
    age INT
);

PREPARE insert_person (person_type) AS
    INSERT INTO people VALUES ($1);

EXECUTE insert_person(ROW('John Doe', 30));

Why Use Prepared Statements?

Performance Optimization: Prepared Statements reduce query processing overhead by preparing and planning queries only once, resulting in faster execution for repeated queries.
Parameterized Queries: Prepared Statements support parameterized queries, allowing for dynamic data input. This not only enhances security by preventing SQL injection but also promotes code reusability.
Execution Plan Reuse: Postgres caches the execution plan of a Prepared Statement, allowing the database engine to reuse the plan for subsequent executions, further contributing to performance gains.
Reduced Network Traffic: Parameter values are sent separately from the query, minimizing network traffic, which is especially beneficial in scenarios with frequent query executions.

Unlocking the Power of PostgreSQL Extensions: A Developer's Toolkit

Caleb Mucheru — Tue, 09 Jan 2024 17:05:09 +0000

While PostgreSQL's core features are robust, its true versatility lies in the extensive ecosystem of extensions. These extensions act as specialized tools, empowering developers to tailor their database experience to specific needs and enhance their workflow. Let's explore some of the most valuable extensions you can leverage:

Apache-AGE
Apache AGE provides graph database functionality. AGE is an acronym for A Graph Extension.To learn more on Apache-AGE from the set-up to using AGE for your database visit the Apache-AGE documentation.
PostGIS
PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium.
pg_stat_statements
pg_stat_statements provides a way to track execution statistics of SQL statements within a PostgreSQL database. This extension is particularly useful for database administrators and developers to analyze the performance of queries and identify potential optimization opportunities.

4.pgcrypto
pgcrypto enables you to encrypt, decrypt, hash, and create digital signatures within your database

Protecting sensitive data is paramount, and pgcrypto is your shield. This extension encrypts data at rest and in transit, safeguarding confidential details from prying eyes. Store hashed passwords securely, encrypt sensitive information like credit card numbers, and perform cryptographic operations – all within the secure enclave of your PostgreSQL database.

5.citext
citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values.

References:

Working with multiple versions of PostgreSQL

Caleb Mucheru — Mon, 23 Oct 2023 15:30:03 +0000

Running different versions of PostgreSQL locally can be useful for both production and development purposes.There are several ways you could achieve this; by using Docker,by specifying the absolute path for the specific version you want to run,and by using pg_wrapper to enable easier switching between different versions of PostgreSQL.
We are going to see how we can use the three methods to achieve this:
Using absolute paths in your terminal
First, we need to have the different versions of PostgreSQL installed. When you run a command like,
pg_config --version, the PostgreSQL version that pops up is the first where the PATH is set first. To run the second version of PostgreSQL,we need to specify the absolute path of its installation. There is an easy way to determine where the installation of either of the PostgreSQL versions is, by finding it in the terminal.
In the terminal, we run
su - to change user to root, then we find it by
sudo find / -name pg_config.
The command return the pg_config absolute paths in which there is a bin directory, for instance, /usr/local/pgsql/bin/pg_config/pg_config.
When we run the absolute path above with the --version parameter
like this /usr/local/pgsql/bin/pg_config/pg_config --version
, we get the version of the PostgreSQL in that path.
We can then set the path for our PostgreSQL version in this way: export PATH=$PATH:/usr/local/pgsql/bin to make initdb postgres to create postgres cluster and later createdb sampledb to create sampledb in the postgres cluster.

Spinning up Apache-AGE on Docker

Caleb Mucheru — Wed, 27 Sep 2023 11:14:38 +0000

This is post recaps on previous posts on using AGE in Docker to entering the psql terminal to using AGE there.
Prerequisites

You need Postgres to be installed. If you do not have it, you can get it from here and build from the source.
Have Docker installed. here

In your PostgreSQL installation's bin folder set up the Postgres server and create you database
initdb postgres
This will give you some command(s) to run.
Create your database:
createdb postgres
Get the apache-age image from the Docker Hub
sudo docker pull apache/age
Run the following command

docker run \
    --name myPostgresDb  \
    -p 5455:5432 \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_PASSWORD=your_password \
    -e POSTGRES_DB=postgresDB \
    -d \
    apache/age

Frome the command above:
--name Assign a name to the container
-p Publish a container’s port(s) to the host
-e Set environment variables
-d Run container in background and print container ID

Run the following command to get into psql terminal
psql -h localhost -p 5455 -U postgres


Then do the following to create and load the extension:
postgres=# CREATE EXTENSION age;
CREATE EXTENSION
postgres=# LOAD 'age';
LOAD
postgres=# SET search_path=ag_catalog, "$user", public;
SET

References:

Setting up a Simple PostgreSQL Database

Caleb Mucheru — Sun, 03 Sep 2023 07:52:29 +0000

PostgreSQL is a robust open-source relational database management system. Setting up a simple PostgreSQL database on your system allows you to interact with the system before before integrating extensions. We will cover some steps, starting with the download and installation of PostgreSQL version 12.15. We are going to do the installation from the source.
Step 1: Download PostgreSQL

Open a terminal in your system.
Clone the repository to your system: We are going to use the unofficial github repository of Postgres from In your terminal run git clone -b REL_12_STABLE https://github.com/postgres/postgres.git This will clone the specified version of PostgreSQL, version 12 from the repo.
Update your package list: sudo apt-get update Step 2: Install PostgreSQL Navigate to the the postgres installation folder Then run the following command to configure your files: ./configure Run this command to install PostgreSQL sudo make install Run the following command to make sure that PostgreSQL will work as expected by developers. make check Step 3: Start the PostgreSQL Service After the installation, PostgreSQL should automatically start. However, you can ensure it's running with this command: sudo systemctl start postgresql This command starts the PostgreSQL service.

Step 4: Create a Database User
By default, PostgreSQL uses the system username to authenticate. To create a PostgreSQL user with your system username and grant necessary privileges, follow these steps:

Access the PostgreSQL interactive terminal: sudo -u postgres psql
In the PostgreSQL terminal, create a user (replace your_username with your actual system username): CREATE USER your_username;
Set a password for the user (replace your_password with your desired password): ALTER USER your_username PASSWORD 'your_password';
Grant superuser privileges to the user: ALTER USER your_username WITH SUPERUSER;
Exit the PostgreSQL terminal by \q Step 5: Create a Database Now, create a database with your user's privileges. In the terminal, run: createdb testdb This command creates a database named "testdb." Step 6: Connect to the Database

Connect to your newly created database using the psql command. Replace your_username with your system username and testdb with the database name if necessary:
psql -U your_username -d testdb
Step 7: Create a Table and Insert Data
Once connected to the database, let’s create a table and insert data

CREATE TABLE employees (
    id serial PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50),
    salary numeric
);

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 50000),
       ('Jane', 'Smith', 60000),
       ('Alice', 'Johnson', 55000);

Step 8: Query the Database

Let’s query the database to retrieve information from the "employees" table
SELECT * FROM employees;
The results of the above query is:

 id | first_name | last_name | salary 
----+------------+-----------+--------
  1 | John       | Doe       |  50000
  2 | Jane       | Smith     |  60000
  3 | Alice      | Johnson   |  55000
(3 rows)

That's it! You've successfully set up a PostgreSQL database, starting from downloading and installing PostgreSQL.