DBA expertise: PostgreSQL, SQL Server & MySQL
Streaming replication is a common PostgreSQL high-availability setup, consisting of a primary server (read-write) and one or more standby replicas (read-only). However, a key limitation of streaming replication is that it doesn’t automatically failover to a replica if the primary server goes down, requiring manual intervention from a DBA to promote the replica to read-write…
the upgrad decided to two part minor upgrade Each PostgreSQL version may require minor upgrades throughout its lifecycle to address critical bugs, enhance performance, or mitigate security vulnerabilities. It’s a best practice to review the release notes and test the new build in a non-production environment before applying it to your production system.additionally you should…
pgBackRest is a backup solution specifically designed for disaster recovery in PostgreSQL databases. Unlike other backup solutions like pg_dump, pgBackRest supports point-in-time recovery and offers many additional features. pgBackRest can perform three types of backups: installing and configuring We will begin by installing pgBackRest on an Ubuntu Server 22 using the following command: sudo apt…
pgbench is tools that used to stress test database base on deferent scenarios you can define by simulating database traffic . pgbench can help use also now our database setup limitationsand also get clear understand how our configuration will be able to tackle live use . i personly use pgbench for two propose 1- in…
When installing PostgreSQL, it defaults to creating its data directory in /var/lib/postgresql. For better performance, reliability, and management, it is often recommended to relocate the PostgreSQL data directory to a dedicated disk or separate mount point. This can help with I/O performance, backup management, and isolation of database operations from the rest of the system. drop…
deferent than stream replication , where the master will send wall log , there we send the actual command such (insert into t1 values (1,”’value” )) physical replication in physical replication , the replica is force to copy the whole database schema tables and so on from master server .physical replication in this case can not…
verify the replication on slave server before we start the failover , we need to verified the syn between the master and slave in the slave run the below command and if its return 0 means no delay SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT(EPOCH FROM now() – pg_last_xact_replay_timestamp()) END AS log_delay;…
prerequisite : master slave hardware specs we will be implement master-slave streaming replication using two VMS running ubuntu 22 jammyboth node will have PostgreSQL 12 installed , and configured with ip in same subnetbelow are VM details .1.postgresqlDB01 : 10.10.10.772.postgresqlDB02 : 10.10.10.78 installing PostgreSQL first check if PostgreSQL 12 is available in Ubuntu repositories using…
type of backup 1. logical backup A logical backup refers to the process of converting the data in a database into a straightforward text format. This typically involves creating scripts of the database, table, or database cluster, which are then saved as SQL files. The scripts for tables and databases are composed of SQL statements….
${toc} type of backup 1. logical backup A logical backup refers to the process of converting the data in a database into a straightforward text format. This typically involves creating scripts of the database, table, or database cluster, which are then saved as SQL files. The scripts for tables and databases are composed of SQL…