PostgreSQL - pglogical 2 extention

1. Overview 

• The pglogical 2 extension provides logical streaming replication for PostgreSQL, using a publish/subscribe model.
• Using pglogical the provider and subscriber must be running PostgreSQL 9.4 or newer.
• Use cases supported are:
• Upgrades between major versions (given the above restrictions)
• Full database replication
• Selective replication of sets of tables using replication sets
• Selective replication of table rows at either publisher or subscriber side (row_filter)
• Selective replication of table columns at publisher side
• Data gather/merge from multiple upstream servers
• Architectural details:
• pglogical works on a per-database level, not whole server level like physical streaming replication
• One Provider may feed multiple Subscribers without incurring additional disk write overhead
• One Subscriber can merge changes from several origins and detect conflict between changes with automatic and configurable conflict resolution (some, but not all aspects required for multi-master).
• Cascading replication is implemented in the form of changeset forwarding.
• Limitations and restrictions
• To replicate multiple databases you must set up individual provider/subscriber relationships for each. There is no way to configure replication for all databases in a PostgreSQL install at once
• UPDATEs and DELETEs cannot be replicated for tables that lack a PRIMARY KEY or other valid replica identity such as a UNIQUE constraint. Replication has no way to find the tuple that should be updated/deleted since there is no unique identifier.
• Automatic DDL replication is not supported. Managing DDL so that the provider and subscriber database(s) remain compatible is the responsibility of the user. 
• The state of sequences added to replication sets is replicated periodically and not in real-time.
• PGLogical can replicate across PostgreSQL major versions. Despite that, long term cross-version replication is not considered a design target, though it may often work. Issues where changes are valid on the provider but not on the subscriber are more likely to arise when replicating across versions. 
• It is safer to replicate from an old version to a newer version since PostgreSQL maintains solid backward compatibility but only limited forward compatibility. Initial schema synchronization is only supported when replicating between same version of PostgreSQL or from lower version to higher version
• ...

2. General information

Master Node

IP: 192.168.56.111

Hostname: postgres-master.taolaoxibup.com

OS: Red Hat Enterprise Linux release 9.4 (Plow)

postgres=# select datname from pg_database ;

  datname  

-----------

 postgres

 dvdrental        // we will replica this database

 template1

 template0

(4 rows)

Slave Node

IP: 192.168.56.222

Hostname: postgres-slave.taolaoxibup.com

OS: Red Hat Enterprise Linux release 9.4 (Plow)

3. Installation on Master/Slave nodes

# dnf install -y postgresql16-server postgresql16-contrib

# dnf install pglogical_16

PostgreSQL - Installation

A. Online - Installation from Binaries

1. Install the repository RPM

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. Disable the built-in PostgreSQL module

sudo dnf -qy module disable postgresql

3. Install PostgreSQL

sudo dnf install -y postgresql16-server

4. Optionally initialize the database and enable automatic start

sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

sudo systemctl enable postgresql-16

sudo systemctl start postgresql-16

5. Install extensions packages

# dnf install postgresql16-contrib.x86_64

MongoDB - Performance Tuning in Linux for DBAs

There are some important elements must be considered carefully for tuning database performance.

1. File Systems

• Using XFS as it generally performs better with MongoDB. 
• With the WiredTiger storage engine, using XFS is strongly recommended for data bearing nodes to avoid performance issues that may occur when using EXT4 with WiredTiger.
• Set 'noatime' on MongoDB data volumes in /etc/fstab

2. Swappiness

• “Swappiness” is a Linux kernel setting that influences the behavior of the Virtual Memory manager. The vm.swappiness setting ranges from 0 to 100: the higher the value, the more strongly it prefers swapping memory pages to disk over dropping pages from RAM
• Set vm.swappiness to 1, not 0
• Allocating RAM as much as possible (storage.wiredTiger.engineConfig.cacheSizeGB)

3. Disable Transparent Huge Pages (THP)

• Transparent Huge Pages (THP) is a Linux memory management system that reduces the overhead of Translation Lookaside Buffer (TLB) lookups on machines with large amounts of memory by using larger memory pages.
• However, database workloads often perform poorly with THP enabled, because they tend to have sparse rather than contiguous memory access patterns. When running MongoDB on Linux, THP should be disabled for best performance

4. Manage Connection Pool Sizes

• Avoid overloading the connection resources of a mongod or mongos instance by adjusting the connection pool size to suit your use case.
• Specify connection pool settings in these locations:
• The MongoDB URI
• Your application's MongoClient instance
• Your application framework's configuration files
• Some parameters control connections in /etc/mongod.conf file:
• net.maxIncomingConnections
• setting parameters with setParameter: option

5. Separate Components onto Different Storage Devices

• For improved performance, consider separating your database's data, journal, and logs onto different storage devices, based on your application's access and write pattern. 
• Mount the components as separate filesystems and use symbolic links to map each component's path to the device storing it.
• For the WiredTiger storage engine, you can also store the indexes on a different storage device by configuring storage.wiredTiger.engineConfig.directoryForIndexes parameter.

PostgreSQL - Upgrade Methods

There are several upgrade database methods, such as:

• Upgrade with downtime
• Using pg_dump and pg_restore
• Using pg_upgrade
• pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5. It is not required for minor version upgrades, e.g., from 12.7 to 12.8 or from 14.1 to 14.5
• pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases
• Using pg_dumpall
• Upgrading with no/near zero downtime using logical/trigger based replication
• PostgreSQL logical replication (v10 and above)
• Known as transactional replication, the subscriber initially receives a copy of the replicated database object from the publisher and pulls any subsequent changes on the same object as they occur in real-time.
• The typical use-cases for logical replication are:
• Sending incremental changes in a single database or a subset of a database to subscribers as they occur.
• Firing triggers for individual changes as they arrive on the subscriber.
• Consolidating multiple databases into a single one (for example for analytical purposes).
• Replicating between different major versions of PostgreSQL.
• Replicating between PostgreSQL instances on different platforms (for example Linux to Windows)
• Giving access to replicated data to different groups of users.
• Sharing a subset of the database between multiple databases.

MongoDB - /etc/mongod.conf sample file

[root@mongodb-01 ~]# cat /etc/mongod.conf 

# mongod.conf

# for documentation of all options, see:

# http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.

systemLog:

  destination: file

  logAppend: true

  path: /var/log/mongodb/mongod.log

# Where and how to store data.

storage:

  dbPath: /u01/mongoDB/helloWorld

  journal:

    enabled: true

  engine: wiredTiger

  wiredTiger:

    engineConfig:

      journalCompressor: snappy

      cacheSizeGB: 1.5            // Adjust base on physical resource

    collectionConfig:

      blockCompressor: snappy

# how the process runs - Controls how MongoDB is managed as a system process.

processManagement:

  fork: true                              # Enable MongoDB to run as a background daemon.

  pidFilePath: /var/run/mongodb/mongod.pid     # Location of the PID file when running as a service.

  timeZoneInfo: /usr/share/zoneinfo