How to install and configure PG Bouncer on Linux x86_64

Install the PGBouncer software

List packages allready installed on the system:

yum list installed |grep bounce

Search repository:

 yum search bounce
=========================================================== Name & Summary Matched: bounce ============================================================
pgbouncer.x86_64 : Lightweight connection pooler for PostgreSQL
=============================================================== Summary Matched: bounce ===============================================================

Install:

 yum install pgbouncer.x86_64

Verify installation:

 yum list installed |grep bounce
pgbouncer.x86_64                                              1.24.0-42PGDG.rhel8                     @YOURREPOSITORY_PostgreSQL_PostgreSQL_common_RHEL8_x86_64

Done with the installation!

Configure the pgbouncer for connections made by the user "music" to my database called "musicdb"

[root] chown postgres:postgres /etc/pgbouncer/ -R

Create the file /etc/pgbouncer/pgbouncer.ini:

;
; pgbouncer config file
;
[databases]
postgres    = port=5432 dbname=musicdb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
admin_users = music
auth_type   = scram-sha-256
auth_file   = /postgres_data/config/users.txt
logfile     = /postgres_data/config/pgbouncer.log
pidfile     = /postgres_data/config/pgbouncer.pid
server_reset_query = DISCARD ALL;
pool_mode = session
default_pool_size = 5
log_pooler_errors = 0

Find the SCRAM authenticatio key:

psql
select '"'||rolname||'" "'||rolpassword||'"' from pg_authid where rolname='music';

  
Create the file /postgres_data/config/users.txt

vi /postgres_data/config/users.txt

Add the following, and paste the value of the key you found in the query above, in between the second pair of quotation marks:

"music" "SCRAM-SHA-256_your_SCRAM_key_here"

Make sure port 6432 isn't occupied

 ss -ltpn | grep 6432 --> no output? Go go ahead! 

As the postgres user, start the pgbouncer process

pgbouncer -d /etc/pgbouncer/pgbouncer.ini

The port will now be occupied:

ss -ltpn | grep 6432
LISTEN 0      128        127.0.0.1:6432      0.0.0.0:*    users:(("pgbouncer",pid=392231,fd=12))

You can now connect to your pgbouncer instance. There are a number of different show-commands:

psql -p 6432 -h 127.0.0.1 -U music pgbouncer -c "show config"
Password for user music:
             key             |                         value                          |                        default                         | changeable
-----------------------------+--------------------------------------------------------+--------------------------------------------------------+------------
 admin_users                 | music                                                  |                                                        | yes
 application_name_add_host   | 0                                                      | 0                                                      | yes
 auth_dbname                 |                                                        |                                                        | yes
 auth_file                   | /var/lib/pgsql/users.txt                               |                                                        | yes

And some control commands:

psql -p 6432 -h 127.0.0.1 -U music pgbouncer -c "reload"
Password for user music:
RELOAD

To connect through the pgbouncer rather than a direct connection, use the syntax below, where you exchange the database name ("musicdb") with "pgbouncer":

  psql -p 6432 -h 127.0.0.1 -U music -d pgbouncer
Password for user music:
psql (15.10, server 1.24.0/bouncer)
WARNING: psql major version 15, server major version 1.24.
         Some psql features might not work.
Type "help" for help.

pgbouncer=# \conninfo
You are connected to database "pgbouncer" as user "music" on host "127.0.0.1" at port "6432".

How to check why a PostgreSQL server won't start

journalctl -u postgresql-15.service

Example output:

Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service entered failed state.
Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service failed.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Starting PostgreSQL 15 database server...
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no postmaster[65528]: 
2024-12-27 06:22:01.703 GMT [65528] 
LOG:  syntax error in file "/path/to/file/postgresql.auto.conf" line
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service: main process exited, code=exited, status=1/FAILURE
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Failed to start PostgreSQL 15 database server.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service entered failed state.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service failed.

In this case, the error seems to be in an error in the file postgresql.auto.conf.

How to use rsync between two servers

For example, to syncronize two servers so they get identical file structures: 

• Source server: server1.oric.no 
• Destination server: server2.oric.no 
• Execute as: root

target=server2.oric.no && rsync -vaz --delete /u01/app/myfiles ebsuser@$target:/u01/app

This will sync the entire "/u01/app/myfiles" folder + all subfolders.

The --delete flag in rsync ensures that files in the destination directory (ebsuser@$target:/u01/app) that are not present in the source directory (/u01/app/inst) are deleted. This helps keep the destination directory synchronized with the source directory.

Error: ORA-16831: operation not allowed on this member

When trying to add a standby database to your configuration, I received

DGMGRL>  add database 'cdb_stby1' as connect identifier is 'cdb_stby1';
Error: ORA-16831: operation not allowed on this member

This message means that the database you are trying to add, in this case cdb_stby1, is not ready to become a standby database in your configuration.

It is easy to verify by querying the "database_role" column in the v$database view.

It needs to be opened with a role that is NOT primary, for example, the PHYSICAL STANDBY role

select database_role, open_mode,name from v$database;

DATABASE_ROLE    OPEN_MODE            NAME
---------------- -------------------- ------------------------------
PHYSICAL STANDBY MOUNTED              CDB

If your database is opened with the role PRIMARY, you need to clone it from your target, for example by using RMANs DUPLICATE ... FOR STANDBY FROM ACTIVE DATABASE

How to use autoconfig to create a sample config file for non-cdb to pdb conversions

java -jar autoupgrade.jar -create_sample_file config /tmp/autoconfig_example.txt noncdbtopdb

Lock and expire users

An anonymous PL/SQL block to lock and expire open accounts:

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /oracle/admin/mydb01/scripts/lockAccount.log append
BEGIN
 FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM') )
 LOOP
IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
  execute immediate 'alter user ' ||
         sys.dbms_assert.enquote_name(
         sys.dbms_assert.schema_name(
         item.USERNAME),false) || ' password expire account lock' ;
 ELSE
  dbms_output.put_line('Locking: ' || item.USERNAME);
  execute immediate 'alter user ' ||
         sys.dbms_assert.enquote_name(
         sys.dbms_assert.schema_name(
         item.USERNAME),false) || ' account lock' ;
 END IF;
 END LOOP;
END;
/
spool off

Arguments that can be passed to dbca - usage output

This can certainly come in handy one day...

        -createDatabase - Command to Create a database.
                -responseFile | (-gdbName,-templateName)
                -responseFile - 
                -gdbName 
                -templateName 
                [-useWalletForDBCredentials  Specify true to load database credentials from wallet]
                        -dbCredentialsWalletLocation 
                        [-dbCredentialsWalletPassword ]
                [-characterSet ]
                [-dvConfiguration  Specify true to configure and enable database vault.]
                        -dvUserName 
                        -dvUserPassword 
                        [-dvAccountManagerPassword ]
                        [-dvAccountManagerName ]
                [-datafileDestination ]
                [-datafileJarLocation ]
                [-runCVUChecks ]
                [-sid ]
                [-redoLogFileSize ]
                [-registerWithDirService ]
                        -dirServiceUserName 
                        [-databaseCN ]
                        [-dirServiceCertificatePath ]
                        [-dirServicePassword ]
                        [-dirServiceUser ]
                        [-ldapDirectoryAccessType ]
                        [-useSYSAuthForLDAPAccess ]
                        [-walletPassword ]
                [-systemPassword ]
                [-nodelist ]
                [-oracleHomeUserPassword ]
                [-sysPassword ]
                [-enableArchive  Specify true to enable archive>]
                        [-archiveLogMode ]
                        [-archiveLogDest ]
                [-memoryMgmtType ]
                [-variables ]
                [-listeners ]
                [-olsConfiguration  Specify true to configure and enable Oracle Label Security.]
                        [-configureWithOID This flag configures Oracle Label Security with OID.]
                [-createAsContainerDatabase ]
                        [-pdbName ]
                        [-numberOfPDBs ]
                        [-pdbStorageMAXSizeInMB ]
                        [-pdbStorageMAXTempSizeInMB ]
                        [-useLocalUndoForPDBs   Specify false to disable local undo tablespace for PDBs.]
                        [-pdbAdminPassword ]
                        [-pdbOptions ]
                [-recoveryAreaDestination ]
                        [-recoveryAreaSize ]
                [-createListener ]
                [-useOMF  Specify true to use Oracle-Managed Files.]
                [-memoryPercentage | -totalMemory]
                [-memoryPercentage ]
                [-totalMemory ]
                [-dbOptions ]
                [-sampleSchema ]
                [-variablesFile ]
                [-customScripts ]
                [-databaseType ]
                [-oracleHomeUserName ]
                [-initParams ]
                        [-initParamsEscapeChar ]
                [-policyManaged | -adminManaged]
                [-policyManaged ]
                        -serverPoolName 
                        [-pqPoolName ]
                        [-createServerPool ]
                                [-pqPoolName ]
                                [-forceServerPoolCreation ]
                                [-pqCardinality ]
                                [-cardinality ]
                [-adminManaged ]
                [-nationalCharacterSet ]
                [-storageType < FS | ASM >]
                        -datafileDestination 
                        [-asmsnmpPassword ]
                [-databaseConfigType ]
                        [-RACOneNodeServiceName ]
                [-emConfiguration ]
                        [-dbsnmpPassword ]
                        [-emPassword ]
                        [-emUser ]
                        [-emExpressPort ]
                        [-omsHost ]
                        [-omsPort ]
                        [-emExpressPortAsGlobalPort ]