fbpx

PostgreSQL

How to Setup Logical Replication PostgreSQL ?

by

Here are the steps you can follow to set up logical replication in PostgreSQL:

  1. Install PostgreSQL on both the publisher and subscriber systems: You will need to install PostgreSQL on both the publisher (i.e., the system that will send the data) and the subscriber (i.e., the system that will receive the data). You can do this using the package manager for your Linux distribution.

  2. Create a publication on the publisher: You will need to create a publication on the publisher system to define which tables and data you want to replicate. You can do this using the CREATE PUBLICATION command:​

				
					CREATE PUBLICATION publication_name FOR TABLE table_name [, ...];
  1. Create a subscription on the subscriber: You will need to create a subscription on the subscriber system to receive the data from the publisher. You can do this using the CREATE SUBSCRIPTION command:
				
					CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION publication_name [, ...];
  1. Start the subscription: You will need to start the subscription to begin replicating data from the publisher to the subscriber. You can do this using the ALTER SUBSCRIPTION command:
				
					ALTER SUBSCRIPTION subscription_name START;
  1. Verify that logical replication is working: You can verify that logical replication is working by checking the pg_stat_subscription view on the subscriber system.

Migrate PostgreSQL From Cantos to Rocky Linux ?

by

Here are the steps you can follow to migrate a PostgreSQL database from CentOS to Rocky Linux:

  1. Install PostgreSQL on Rocky Linux: You will need to install PostgreSQL on your Rocky Linux system if it is not already installed. You can do this using the package manager for your Linux distribution.

  2. Stop the PostgreSQL service on CentOS: You will need to stop the PostgreSQL service on your CentOS system to ensure that the data is not modified during the migration process. You can do this using the systemctl command:

				
					systemctl stop postgresql
  1. Create a backup of the PostgreSQL data: You will need to create a backup of the PostgreSQL data on your CentOS system to ensure that you have a copy of the data that you can restore on Rocky Linux. You can use the pg_dump command to create a backup of the data:
				
					pg_dump -U username -W -Fc database_name > database_name.dump
  1. Transfer the backup file to Rocky Linux: You will need to transfer the backup file to your Rocky Linux system. You can use a tool like scp to copy the file from CentOS to Rocky Linux:
				
					scp database_name.dump username@rocky_linux_server:/path/to/destination
  1. Restore the backup on Rocky Linux: You can use the pg_restore command to restore the backup file on your Rocky Linux system:
				
					pg_restore -U username -W -d database_name database_name.dump
  1. Start the PostgreSQL service on Rocky Linux: Once the data has been restored, you can start the PostgreSQL service on your Rocky Linux system:
				
					systemctl start postgresql

I hope this helps! Let me know if you have any questions.

Redis Docker Compose with a Persistent Volume

by

Here is a sample Docker Compose file that you can use to set up Redis with a persistent volume using Docker:

				
					version: '3'

services:
  redis:
    image: redis:latest
    volumes:
      - redis-data:/data
    ports:
      - "6379:6379"

volumes:
  redis-data:
    driver: local

This Docker Compose file defines a single service: Redis. It specifies the Docker image to use for the service and maps the necessary ports to enable communication with the database. It also defines a volume called redis-data to store the data for the Redis database.

To use this Docker Compose file, save it to a file (e.g., docker-compose.yml) and run the following command:

				
					docker-compose up

This will start the Redis container and bring up the database. The data stored in the Redis database will be persisted in the redis-data volume, so it will be preserved even if the Redis container is stopped or removed.

I hope this helps! Let me know if you have any questions.

How to Take PostgreSQL Schema Backup

by

In some cases, we have to take PostgreSQL schema level backup. it’s like a logical backup where it just backup the schema objects like tables, indexes, and procedures. These backups are used in the condition where you need to restore just one schema backup, not the whole database. For daily backup, you can create a shell or bash script and schedule it using crontab or windows scheduler.

Script For Schema Backup 

				
					#!/bin/bash
mv /tmp/db_list.log /tmp/db_list.log_old
psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" > /tmp/db_list.log ; sed -i '/^$/d' /tmp/db_list.log
file=/tmp/db_list.log
#schema_name
#DB
date=$(date '+%Y-%m-%d')
bkp_path=/var/lib/pgsql

for DB in `cat $file`
do
echo "#################Starting the backup as on ${date} for ${DB}"
pg_dump -U postgres -v -n '*' -N 'pg_toast' -N 'information_schema' -N 'pg_catalog' $DB -Ft -f ${bkp_path}/${DB}_schema_bkp_${date}.tar > `${bkp_path}/${DB}_schema_bkp_${date}.log)`
echo "#################Backup Complited as on ${date} for ${DB}"
done

echo "listing files older than 15 Days"
find ${bkp_path} -name "*schem*.tar" -type f -mtime +1 > ${bkp_path}/old_file_list.log
find ${bkp_path} -name "*.log" -type f -mtime +1 > ${bkp_path}/old_file_list.log

echo "deleting backup/log file older than 15 Days"
find ${bkp_path} -name "*.tar" -type f -mtime +15 > ${bkp_path}/old_file_list.log -delete

In this script, we have excluded system schemas like ‘information_schema’ and many more. Please remove ‘-N and schema Name if you need a backup system schema.’ It will start including the schema backup in the tar file.

How to execute the Script ?

				
					chmod 777 backup_schema.sh
./backup_schema.sh

Read More...

How to Fix Pg_ctl command not found

by

Sometimes after a fresh PostgreSQL install if we want to start or stop from PostgreSQL rather than controlling the PostgreSQL instance start and stop from the root. And when we run the pg_ctl from the command prompt as a PostgreSQL Linux user we see the error bash: pg_ctl: command not found… 

In this article, lets learn how to fix it

As PostgreSQL check your pg base directory 

 

And now add this PostgreSQL to the Linux path so that it’s always available. I would recommand to create a postgresql env. file so that it more easy to handle rather than messing with other systems runing on linux operating system.

				
					--Step 1 
-bash-4.2$ echo $PGDATA
/var/lib/pgsql/10/data

--Step 2
-bash-4.2$ echo "PATH=/usr/pgsql-10/bin:$PATH">>~/.postgre_10.profile

--Step 3
chmod 777 .postgre_10.profile

--Step 4 
. .postgre_10.profile

--Step 5
Check pg_ctl now

-bash-4.2$ pg_ctl --version
pg_ctl (PostgreSQL) 10.17
-bash-4.2$

Free Database Client For PostGresql

by

1. pgAdmin

Single pack with Database client, multiple Database Administration tools and monitoring capabilities. You can also perform Backup and recovery from this tool.

2. dbeaver

One of my favourite tools to query remote databases with 100 different database connectivity options. And its 100% free with frequent big fixed and updates 

3. HeidiSQL

OpenSource and Rich database client with many database connectivity options. Frequent bug fixed and stable.

Read More...