Thursday, July 25, 2024

PostgreSQL - Extensions

1. Check for available extensions
edb=# select * from pg_available_extensions;

2. Check for installed extensions
edb=# select * from pg_available_extensions where installed_version is not null;
edb=# select * from pg_extension;
edb=# \dx

3. Some important extensions
pg_stat_statements provides a means for tracking planning and execution statistics of all SQL statements executed by a server. When pg_stat_statements is active, it tracks statistics across all databases of the server

dblink is used to connect to other PostgreSQL databases from within a database

postgres_fdw can be used to access data stored in external PostgreSQL servers. postgres_fdw is the successor of the old dblink extension

oracle_fdw extension is a foreign data wrapper that allows you to access Oracle table and views (including materialized views) via foreign tables.

pgcrypto is a PostgreSQL extension that provides cryptographic functions and capabilities directly within the database. It enhances data security by allowing various cryptographic operations to be performed within SQL queries.

pg_cron is a simple cron-based job scheduler that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database

pgAudit is an extension for PostgreSQL that provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL. It is designed to help database administrators and developers meet security and compliance requirements by providing detailed information on database activities.

pg_partman is an extension that simplifies creating and maintaining partitions of your PostgreSQL tables.

pglogical extension provides logical streaming replication for PostgreSQL, using a publish/subscribe module.

pg_buffercache module provides a means for examining what's happening in the shared buffer cache in real time.

pg_freespacemap module provides a means for examining the free space map (FSM). It provides a function called pg_freespace, or two overloaded functions, to be precise. The functions show the value recorded in the free space map for a given page, or for all pages in the relation

Pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client.

pgBouncer is a Lightweight connection pooler for PostgreSQL

system_stats extension is a library of stored procedures that allow users to access system-level statistics for monitoring Postgres activity. These procedures reveal a variety of system metrics to the database server.

Ref:

Wednesday, July 24, 2024

PostgreSQL - Vacuum Processing

Vacuum processing is a maintenance process that facilitates the persistent operation of PostgreSQL. Its two main tasks are removing dead tuples and the freezing transaction ids.

Vacuum parameters:

[enterprisedb@edb-saigon ~]$ less /u01/edb/as16/data/postgresql.conf
...
#------------------------------------------------------------------------------
AUTOVACUUM
#------------------------------------------------------------------------------

#autovacuum = on                                                                    # Enable autovacuum subprocess?  'on' & requires track_counts to also be on.
#autovacuum_max_workers = 3                                        # max number of autovacuum subprocesses & (change requires restart)
#autovacuum_naptime = 1min                                           # time between autovacuum runs
#autovacuum_vacuum_threshold = 50                               # min number of row updates before vacuum
#autovacuum_vacuum_insert_threshold = 1000                # min number of row inserts before vacuum; -1 disables insert vacuums
#autovacuum_analyze_threshold = 50                                # min number of row updates before analyze
#autovacuum_vacuum_scale_factor = 0.2                          # fraction of table size before vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2               # fraction of inserts over table size before insert vacuum
#autovacuum_analyze_scale_factor = 0.1                           # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000                        # maximum XID age before forced vacuum & (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000    # maximum multixact age before forced vacuum & (change requires restart)
#autovacuum_vacuum_cost_delay = 2ms                              # default vacuum cost delay for autovacuum, in milliseconds; & -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1                                   # default vacuum cost limit for autovacuum, -1 means use vacuum_cost_limit
...

Wednesday, July 10, 2024

EDB - Replication Server (xDB) - Important Concepts

Snapshot and synchronization overview

Replication Server performs two different types of replications. These two main types are called snapshot replication and synchronization replication.

In either method, the source tables refer to the tables from which the replication data is originating. In a single-master replication system, it's the publication. In a multi-master replication system it's the primary node whose changes are being replicated to another primary node.

The target tables are the tables that are receiving the replication data from the source tables. In a single-master replication system, it's the subscription tables. In a multi-master replication system, it's the primary node receiving changes from another primary node.

In snapshot replication, all existing rows in the target tables are deleted using the database system’s TRUNCATE command. The tables are then completely reloaded from the source tables of the publication.

In synchronization replication, only the changes (inserts, updates, and deletions) to the rows in the source tables since the last replication are applied to the target tables.

Note
Deleting all rows in a source table executed by the SQL TRUNCATE command results in replication to the target tables only if the log-based method of synchronization replication is used. If the trigger-based method of synchronization replication is used, executing the TRUNCATE command on a source table doesn't replicate the effect to the target tables. You must perform a snapshot from the source table to the target tables if you use the trigger-based method.

Synchronization replication is implemented using two different methods: the trigger-based method and the log-based method.

In the trigger-based method, changes to rows in the source tables result in row-based triggers executing. These triggers record the changes in shadow tables. The changes recorded in the shadow tables are then periodically extracted from the shadow tables, converted to an in-memory data structure, and applied to the target tables by means of SQL statements executed using JDBC. See Synchronization replication with the trigger-based method for information on the trigger-based method.

In the log-based method, changes to rows in the source tables are extracted from the write-ahead log segments (WAL files) using asynchronous streaming replication implemented by the logical decoding feature available in Postgres database servers. The extracted changes are converted to an in-memory data structure and applied to the target tables by means of SQL statements executed using JDBC. See Synchronization replication with the log-based method for information on the log-based method.

Tuesday, July 02, 2024

EDB - Replication Server (xDB) - Installation

Introduction

Replication Server is an asynchronous replication system available for PostgreSQL and for EDB Postgres Advanced Server.

You can use Replication Server to implement replication systems based on either of two different replication models: single-master (primary-to-secondary) replication or multi-master replication. Regardless of the replication model, Replication Server is flexible and easy to use.

For single-master replication, PostgreSQL, EDB Postgres Advanced Server, Oracle, and Microsoft SQL Server are supported in an assortment of configurations (including cascading replication), allowing organizations to use it in multiple use cases with a variety of benefits. The following are some combinations of cross database replications that Replication Server supports for single-master replication:
  • From Oracle to PostgreSQL
  • From Oracle to EDB Postgres Advanced Server
  • From SQL Server to PostgreSQL
  • From SQL Server to EDB Postgres Advanced Server
  • From EDB Postgres Advanced Server to Oracle
  • From PostgreSQL to SQL Server
  • From EDB Postgres Advanced Server to SQL Server
  • Between PostgreSQL and EDB Postgres Advanced Server
  • From PostgreSQL to Oracle (WAL mode)
  • From PostgreSQL to Oracle (trigger mode)

EDB - PEM - Installation

Theory
  • You can install PEM on a single server, or you can install the web application server and the backend database on two separate servers. In this post, we will install PEM on a single server.
  • Because installing without internet, so we must config LOCAL yum server. Please check via Create Local HTTP Yum/DNF Repository
Install the package
[root@edb-pem ~]# dnf install edb-as16-server
[root@edb-pem ~]# dnf install edb-as16-server-sslutils   //using for EDB Postgres Advanced Server
[root@edb-pem ~]# dnf install edb-pem

Change password of enterprisedb user
[enterprisedb@edb-pem ~]$ psql edb
edb=# \conninfo 
You are connected to database "edb" as user "enterprisedb" via socket in "/tmp" at port "5444".

edb=# \password 
Enter new password for user "enterprisedb": 

Configure authentication on the backend database
Modifying the pg_hba.conf file to allow pem to connect to database.
    
Initial configuration
[root@edb-pem ~]# /usr/edb/pem/bin/configure-pem-server.sh
...
 -----------------------------------------------------
 EDB Postgres Enterprise Manager
 -----------------------------------------------------
 Install type: 1:Web Services and Database, 2:Web Services 3: Database  1
 Enter local database server installation path (i.e. /usr/edb/as12 , or /usr/pgsql-12, etc.)  /usr/edb/as16
 Enter database super user name  enterprisedb
 Enter database server port number  5444
...
Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24  0.0.0.0/0
Enter database systemd unit file or init script name (i.e. edb-as-12 or postgresql-12, etc.)  edb-as-16
Please specify agent certificate path (Script will attempt to create this directory, if it does not exists)  /root/.pem/
Please specify PEM web server (openssl) certificate subject, template: /C=CountryCode/ST=StateName/L=CityName/O=CompanyName/OU=CompanySectionName/CN=CommonNameOrHostname/emailAddress=EmailAddress  /C=VN/ST=HoChiMinh/L=HCM/O=CHERRY/OU=VHUD/CN=edb-pem/emailAddress=khochiutronglong@gmail.com
...
...
...
-->  [Info] -->  [Info] Executing systemctl start httpd
-->  [Info] -->  [Info] Configured the webservice for EDB Postgres Enterprise Manager (PEM) Server on port '8443'.
-->  [Info] -->  [Info] PEM server can be accessed at https://127.0.0.1:8443/pem at your browser


Ref: