DB Notes

Initial Steps Summary Let’s sum up the initial steps, then dive into key points: Ensure that the primary and replica servers have proper firewall configurations. Both servers should be able to communicate over PostgreSQL service ports (default: 5432 ). Edit pg_hba.conf on the Primary Database by adding the following line to allow the replica server to connect: host replication rep_user /32 scram-sha-256 Create a replica user on the Primary Database with the following command: createuser -U postgres rep_user -P --replication -p Edit the postgresql.conf file on the Primary Database: wal_keep_size = 10000 MB max_slot_wal_keep_size = 10000 MB Note: Adjust these values based on your needs. For a high data load, increase the value. For a low data load, decrease it. The above values assume that, at most, 10GB of data might fail to be sent during an outage. Key Points 1. Handling Large Databases For large databases, replication may take hou...

 When we need to connect somewhere via cron job, batch script etc. , .pgpass file can be really useful for authorization. Connection information is saved within the .pgpass file, allowing us to connect to database without password prompts. General text format: host:port:db_name:user_name:password You can also put asterisk(*) to first 3 parameters to match anything. Example .pgpass file content: 192.168.1.40:5432:postgres:myadmin:On3GoodP@ssW0rD Running commands to activate for pg_basebackup (Linux Environment): $ echo "192.168.1.40:5432:*:rep_user:V3ryStr0ngP4ss" > /var/lib/pgsql/.pgpass $ chown postgres.postgres /var/lib/pgsql/.pgpass $ chmod 0600 /var/lib/pgsql/.pgpass $ su - postgres $ /usr/pgsql-14/bin/pg_basebackup -h 192.168.1.40 -D /pgdata/14/data/ -U rep_user -p 5432 -v -P --wall-method=stream --write-recovery-conf