Streaming replication setup on postgres 9.6

Postgres streaming replication in 7 steps.

1. Create replication user on primary and grant ‘REPLICATION’ and ‘LOGIN’ privilege.

reporting=# CREATE ROLE replication WITH REPLICATION PASSWORD 'replUs3r';
CREATE ROLE
reporting=# alter user replication login;
ALTER ROLE

2. PostgreSQL.conf changes on the primary

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5 
wal_keep_segments = 32 
archive_mode = on
archive_command = 'test ! -f /var/untd/pgsql/archive/%f && cp %p /var/untd/pgsql/archive/%f'

listen_addresses variable is required to allow all hosts to connect to this database.

wal_level is minimal by default, we need to set it to replica so that PostgreSQL server logs all changes to transaction logs (xlogs).

wal_keep_segments specifies the number of past log file segments to be kept in pg_xlog directory. If the standby server falls far behind than these many segments, replication breaks. Read More

Oracle get column ddl from dba_tab_columns dictionary view

Query to generate alter table add column DDL from Oracle dictionary views:

There will be a situation when we might need to generate add column DDLs for a particular table or all tables in a schema.

Oracle data dictionary view dba_tab_columns/all_tab_columns stores column details such as

DATA_TYPE – column data type

DATA_LENGTH – Length of the column

DATA_PRECISION – Decimal precision for NUMBER type

DATA_SCALE – Digits to right side of decimal point in NUMBER type

Below example covers following data types: Read More