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

slony add tables to existing set

Steps to add new tables to slony replication

In order to add a new table to existing set in slony, we need to create a new set with the table and merge it with the existing set.

  • Before adding the table to slony, verify below things
    • Table exists on the target node.
    • Table has a primary key. Slony doesn’t work without a primary key.
    • It is better to truncate the table on the target node before adding it to slony replication.
  • Verify existing slony configuration. We need to capture below details.
    • Cluster name
    • Source/target node connection info
    • Existing Set ids, Table ids

Cluster name is same as slony schema name without the underscore at the beginning, it is ‘testslony’ in our case.

Source and target node details from below query Read More

Postgres grant select access on all tables in a database

It is not possible to create a user with read only access on a particular database. Instead we can do it at schema level.

1) Grant connect to database

GRANT CONNECT ON DATABASE <db name> to <user name>;

2) Grant usage on schema

GRANT USAGE ON SCHEMA <schema name> TO <user name>;

3) Grant select on all tables in a schema using

GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user name>;

4) Also make this privilege available for new tables added in future using

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;

 

So, if we have many schemas we can simply generate ‘GRANT’ statements as mentioned below : Read More