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

13.5.0 Enterprise Manager Cloud Control - Patching RU22 from Base version

Overview of the Enterprise Manager Proactive Patch Program (Doc ID 822485.1)

 

List of patches:

• OMS – RU 22
• Patch 36335368: 13.5.0 Enterprise Manager Cloud Control Base Platform Monthly Release Update (RU) 22
• Patch 19999993: OMSPatcher patch of version 13.9.5.20.0 for Enterprise Manager Cloud Control 13.5.0.0.0
• Patch 28186730: OPATCH 13.9.4.2.15 FOR EM 13.5 AND FMW/WLS 12.2.1.4.0 and 14.1.1.0.0
• Patch 35430934: MERGE REQUEST ON TOP OF 12.2.1.4.0 FOR BUGS 32720458 33607709
• Patch 34153238: HTTPS PROXY CONFIGURATION IS NOT USED WHEN PROTOCOL IS CONFIGURED TO TCP
• Patch 31657681: THREADS CONTEND FOR LOCK IN LOADFILEBASEDKEYSTORE WHEN OPENING TLS/SSL ENABLED JDBC CONNECTIONS
• Agent – RU 22
• Patch 36335371: Oracle Enterprise Manager 13c Release 5 Update 22 ( 13.5.0.22 ) for Oracle Management Agent
• Patch 33355570: AgentPatcher release of version 13.9.5.8.0 for Enterprise Manager Cloud Control Agent 13.5.0.0.0

Notes:

• Backup Database and Application before Patching
• Apply OMS RU 22 before applying Agent RU 22
• Apply OMSPatcher and OPatch the latest version before applying OMS RU 22
• Apply Oracle Database to the latest version of Oracle Database 19c before applying OMS RU 22

Main steps:

• Apply Oracle Database to the latest version of Oracle Database 19c
• Apply OMSPatcher and Opatch to the latest version
• Apply OMS RU 22
• Apply Agent RU 22