Streaming Replication in PostgreSQL – The Ultimate Guide for DBAs
Hey, my DBA friends! Whats up?
Let me ask you this: how many times have you had your heart racing, wondering if your database is safe during a failure? Don’t worry; we’ve all been there. 😅
Imagine this scenario, suppose you’re working for a top MNC, and suddenly, the primary server goes down. Panic sets in. But wait, your standby server is already up and running because you set up “streaming replication”. Disaster averted, applause incoming😋👏!
Imagine you’re managing databases for a company like Flipkart. With millions of daily transactions, any downtime could mean a loss of revenue and trust. By setting up streaming replication, Flipkart ensures their operations run smoothly, even during unexpected server failures or maintenance.
Streaming replication isn’t just a technical term; it’s like having a twin for your primary database, ready to take over at a moment’s notice. Whether you’re working with e-commerce giants, banks, or IT service companies, “Streaming replication in PostgreSQL” is your best friend when it comes to keeping databases available and reliable. And the best part? Setting it up is easier than you think! Let me guide you through the process step by step.
What is Streaming Replication?
In simple terms, streaming replication is a process where a “standby server” keeps a live copy of the “primary server’s data” by continuously receiving updates. If the primary server goes down, the standby can quickly take over with minimal downtime.
Here’s why it’s important:
- High availability: Your services keep running even if the primary server crashes.
- Data safety: Data remains protected with real-time backups.
- Scalability: You can use the standby for read-only operations, reducing the load on the primary server.
How to Set Up Streaming Replication in PostgreSQL
| Server | Hostname | IP | Role |
|---|---|---|---|
| Node1 | node1 | 192.168.56.50 | Primary |
| Node2 | node2 | 192.168.56.60 | Standby |
Install same version postgres on both side. remove data directory from standby.
Let’s break it down into simple steps:
Step 1: Configure the Primary Server
1. Edit postgresql.conf :
Enable replication settings:
ALTER SYSTEM SET listen_addresses = '*'; ALTER SYSTEM SET wal_level = 'replica'; ALTER SYSTEM SET max_wal_senders = 10; ALTER SYSTEM SET max_replication_slots = 10; ALTER SYSTEM SET wal_keep_size = '512MB'; ALTER SYSTEM SET hot_standby = 'on';
2. Update pg_hba.conf:
Allow the standby server to connect:
# IPv4 local connections: host all postgres 192.168.56.60/32 scram-sha-256 # replication privilege. host replication postgres 192.168.56.60/32 scram-sha-256
3. Create a replication user:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicator_password';
4. Restart the PostgreSQL service.
Step 2: Take a Backup of the Primary Server
Use the pg_basebackup tool to copy data from the primary server:
pg_basebackup -h 192.168.56.50 -p 5432 -D /var/lib/pgsql/15/data -P -R -X stream
Step 3: Set Up the Standby Server
1. Copy the postgresql.conf and pg_hba.conf files from the primary server to the standby server.
2. Create a recovery.conf file (or standby.signal for PostgreSQL 12+) and also verify postgresql.auto.conf file configuration. -R will create standby.signal file and add primary configuration automatically.
primary_conninfo = 'user=postgres password=postgres channel_binding=prefer host=192.168.56.50 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
3. Start the PostgreSQL service on the standby server.
Step 4: Test the Replication
1. Verify replication status on the primary server:
sql SELECT * FROM pg_stat_replication;
2. Simulate a failover: Stop the primary and promote the standby:
pg_ctl promote
__________________________________________________
So, guys! This is it🧐!
Streaming replication is one of the most valuable tools in a DBA’s arsenal. It ensures high availability, keeps your data safe, and builds confidence in your system’s reliability.
If you’re a PostgreSQL DBA or aspiring to become one, mastering this concept is a must. So, why wait? Roll up your sleeves, start setting it up, and enjoy the peace of mind that comes with a reliable database system. Your clients (and your boss!) will thank you.
Let me know your thoughts, and feel free to share your own experiences with replication! 😊
Conclusion
At Learnomate Technologies, we specialize in delivering the best training for PostgreSQL and other database technologies. Whether you’re a beginner or looking to advance your skills, our expert-led programs are designed to make you industry-ready.
Want to dive deeper into topics like streaming replication and other PostgreSQL features? Check out our YouTube channel for insightful tutorials: www.youtube.com/@learnomate.
Explore more about our PostgreSQL training program on our website here: https://learnomate.org/training/postgresql-training/.
Don’t forget to connect with me on LinkedIn for regular updates and discussions on trending tech topics: https://www.linkedin.com/in/ankushthavali/.
If reading blogs is your thing, we’ve got you covered. From PostgreSQL to other trending technologies, our blog page is packed with helpful content. Start exploring here: https://learnomate.org/blogs/.
Let’s keep learning and growing together!
Happy Reading!
ANKUSH😎
