Tagged Questions
10
votes
3answers
2k views
Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?
Context:
Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the ...
10
votes
2answers
2k views
Streaming replication and failover on PostgreSQL
I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
9
votes
1answer
944 views
What is the difference between PostgreSQL 9.0 Replication and Slony-I?
Is there still any use for Slony-I, and if so, what is it? For clarification, from 9.0 Postgresql supports built in streaming replication.
7
votes
1answer
2k views
PostgreSQL Failover - What tools should I use?
Here is the scenario:
There are two machines running CentOS 6.2 - machine0 and machine1
Both have PostgreSQL 9.1 installed.
One of them should be active, as a master system and through asynchronous ...
7
votes
1answer
3k views
PostgreSQL failover and replication
I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.
I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I ...
7
votes
1answer
84 views
How do data checksums interact with streaming replication?
Data checksums are a new feature introduced in 9.3, and:
there is a new GUC parameter "ignore_checksum_failure" which will force PostgreSQL to continue processing a transaction even if corruption ...
6
votes
1answer
1k views
Is PostgreSQL replication production ready?
How does PostgreSQL native replication compare to MySQL?
I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?
...
6
votes
2answers
1k views
PostgreSQL: Can I do pg_start_backup() on a live, running db under load?
Our established replication has broken ("requested WAL segment has already been removed" during downtime)
We cannot easily stop the master again.
Can we do
pg_start_backup(),
rsync ${PGDATA}/ ...
6
votes
1answer
160 views
Can we use old primary to sync with standby after failover in PostgreSQL?
If my standby (postgres) is behind by few seconds before failover. What is the easiest way to sync back my old primary after failover in PostgreSQL.
In Oracle we have the option of Reinstating a ...
5
votes
1answer
2k views
PostgreSQL: Unable to run repmgr cloned database
I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave.
I am using the instructions on this guide: ...
5
votes
1answer
837 views
How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?
I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
5
votes
2answers
403 views
Best practices to run time offset delayed replication
I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 ...
5
votes
1answer
612 views
PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly
We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
5
votes
1answer
140 views
How to schedule PostgreSQL replication?
I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I ...
4
votes
2answers
6k views
Easy Postgresql master/slave replication with automatic failover on Linux?
I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the ...
4
votes
1answer
3k views
Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)
First, I'm not a DBA, so pardon me if any of this question seems "off."
I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making.
...
4
votes
1answer
802 views
What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?
I have a streaming replication setup between two postgres servers (master: server A, slave: server B). I'm wondering what's actually happening under the hood when I touch the trigger file and the ...
4
votes
1answer
2k views
PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID
For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and ...
4
votes
1answer
1k views
How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?
I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege.
db=> create role replicator login password 'something';
CREATE ROLE
db=> alter ...
4
votes
1answer
200 views
How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication?
We have a fairly simple setup to replicate from our on-premise master PostgreSQL database to our presentation tier in AWS. We're using WAL shipping using the archive_command setting. Basically the ...
4
votes
1answer
298 views
Streaming Replication Failover - how to point second slave at new master?
I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over.
For the cluster itself, I have three identical Postgres 9.2 servers set up like so:
Server A - ...
4
votes
1answer
145 views
PostgreSQL replication for archiving
I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice.
I currently use ...
4
votes
1answer
241 views
Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?
The pg_xlog directory for my master cluster looks like this:
-rw------- 1 postgres postgres 16M Jun 21 21:42 000000010000000000000001
-rw------- 1 postgres postgres 16M Jun 21 22:42 ...
3
votes
2answers
1k views
Running pg_dump on a hot standby server?
Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask.
I would like to run a nightly backup job (via pg_dumpall) from a hot ...
3
votes
2answers
217 views
Replicating some tables from one postgres database to another
I've got the following situation: I have three machines running postgresql databases. One machine holds client account information (call this machine C), the other two machines hold client logging ...
3
votes
2answers
111 views
How can streaming replication be kept lagged by a certain 'delay' time?
Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). ...
3
votes
1answer
546 views
Is replication from SQL Server 2008 to PostgreSql possible?
Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
3
votes
2answers
536 views
PostgreSQL: Can the archive command for master and standby point to the same directory?
I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server.
The only difference in the files is the ...
3
votes
1answer
70 views
Can Postgres synchronous replication stall a cluster if secondary/standby crashes?
In a setup with Postgres synchronous replication, let's say one of the secondary/standby machines crashes, will that lock/halt the master? That is, since the Postgres master always waits for ...
3
votes
2answers
304 views
Postgres Streaming Replication lagging, using lots of CPU and little I/O
We have 2 dedicated DB Servers. One is master and other is its streaming slave.
Postgres version 9.0.17.
We have similar hardware on both servers and using 15krpm SAS drive in Raid 10 for Database ...
3
votes
1answer
194 views
Witnessing standby failure with repmgr
We're setting up a PostgreSQL cluster, and we're looking at using repmgr to handle standby promotion and so on.
Our cluster has one master and a hot standby, and uses the streaming replication ...
3
votes
1answer
460 views
Solution for deploying multiple instances of Django Server with Database replication
have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple ...
3
votes
2answers
1k views
Removing Archive Logs after PostgreSQL PITR online-recovery
I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2.
Replication mode is "on"
Load balance mode is "on" (between primary and standby1)
I followed the official ...
3
votes
2answers
1k views
Streaming Replication in PostgreSQL
I'm trying to set up two PostgreSQL servers on one machine and perform a streaming replication. I have succeeded once, but when i've tried again following exactly the same steps it doesn't work.. ...
3
votes
0answers
38 views
Postgres multidisk master replicating to single disk slave
I have an odd situation. I have a postgreSQL database master with multiple large tablespaces divided across 3 physical disks. I am setting up a slave that will be used to offload long, ...
2
votes
2answers
276 views
Streaming Replication Log is puzzling me
I have a streaming replication hot-standby setup, which seems to be running all good. I can login, execute selects and the result seems to be up-to-date with the master. However in the log of the ...
2
votes
2answers
248 views
Can I replicate views with Slony?
Can I feed some SQL that creates a view to Slony?
I have some SQL that does CREATE OR REPLACE VIEW name_of_view and so on, passes it to slonik_execute_script, and then tries to run slonik_create_set ...
2
votes
1answer
144 views
Postgres: Purpose of archiving in master?
I am reading tutorials on how to setup streaming replication in Postgres from master to slave.
Some tutorials recommend setting up archiving in the master (and restoring in slave) . In the master, ...
2
votes
1answer
609 views
Cannot get my slave - replication server to start
My replication server will not start. I have followed the instructions here:
http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/
As well as several other ...
2
votes
1answer
944 views
Syncing two (or more) PostgreSQL 9.1 clusters
This question assumes the use of PostgreSQL 9.1.
Say I have two PostgreSQL database clusters. Each cluster contains three PostgreSQL servers. A master, a slave synced via synchronous replication and ...
2
votes
1answer
305 views
How to perform asynchronous cascade slave replication of PostgreSQL database?
This is a feasibility/architectural question (I am no database administrator). Let's imagine a PostgreSQL database running online (like those from Heroku for example).
One would like to maintain a ...
2
votes
3answers
1k views
Replicating only significant records to specific database
I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can ...
2
votes
1answer
37 views
PostgreSQL Automatic Failover (Trigger File Creation)?
Is there a way to detect if the master PostgreSQL (9.3) server becomes unresponsive, and if so, create the trigger file (that is specified in recovery.conf) that initiates the failover process?
Or ...
2
votes
1answer
31 views
How can I know whether postgres is currently running synchronous or asynchronous replication?
I see from ps that:
postgres: wal sender process replicator X.X.X.X(38681) streaming 0/17AF5438
But is there any way for me to know whether it is synchronous or asynchronous? I made the relevant ...
2
votes
1answer
689 views
How to switch master server and warm standby (with streaming replication) in PostgreSQL?
I would like to know what is the way to switch the role of 2 servers when using warm standby?
I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with ...
2
votes
1answer
198 views
PostgreSQL asynchronous replication (and possible data loss)
I'm trying to wrap my head around Postgres' asynchronous replication. The way I'm reading the documentation, if the master goes down after data was committed, but before it's shipped to slaves, the ...
2
votes
3answers
362 views
Replication chaining with PostgreSQL
We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized ...
2
votes
1answer
2k views
PostgreSQL replication: out-of-sequence timeline ID, when former master made slave
I've configured master slave replication in PostgreSQL, following this tutorial:
http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Binary_Replication_in_6_Steps
It worked fine. Now I've ...
2
votes
2answers
162 views
postgreSQL 9.3 streaming replication delay
I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of ...
2
votes
1answer
142 views
Postgresql 9.2.6 ignoring archive_command
I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between.
My ...
