**Objective : Set up two DB server if first server down then second can be up and running provided condition is that at evening time both server need to sync. with each other** I am new in DBA and we are using PostgreSQL 9.6 and thinking to use PostgreSQL 10.0. We have regular data in tables along with that we have large spatial data (GIS related data having 'postgis' extension) on Windows Platform We checked few replication tools in which 'Slony' tool is comes with default installation hence we want to use for replication. we are checking this link for reference as well as documentation. If anyone knows proper technical article including required steps then it would great!!

My setup looks something like this: I have a primary server (Postgres 9.6) that continually scrapes a bunch of data from the web. I'd like to be able to stream a subset of tables or databases from that primary into a secondary instance so I can read the data from a web app. The web app needs to do standard web app things with its database (read/write tables for user accounts, perform authentication, etc.) and also be able to run queries on the replicated data. [Streaming replication with a hot standby](https://www.postgresql.org/docs/9.6/static/warm-standby.html#STREAMING-REPLICATION) looked like it would be up my alley, but [a Reddit response from a few years ago](https://www.reddit.com/r/PostgreSQL/comments/3a5976/partial_streaming_replication/#thing_t1_cs9gdht) seems to suggest that you can't filter for specific tables or write to the replicated cluster at all. From poking around on the Internet, my impression is that most people solve this problem with third-party tools like Bucardo and Slony – I'm open to that, but I'm looking for the simplest possible solution and adding a dedicated replication system to my stack seems unnecessarily intense. [This SO question from last year](https://stackoverflow.com/questions/32407764/its-possible-to-use-logical-decoding-to-replicate-a-single-table) had a promising lead using logical decoding, which makes me wonder if there is any new work in that direction. I'm also wondering if it would work to just run two instances of Postgres and bind them to different ports. Are there any solutions here that don't require installing third-party tools? If a third-party tool is absolutely necessary, which one would have the lowest overhead for my purposes, from your experience? Thanks in advance for the help, y'all. First question here and I'm thrilled to have access to your expertise.

Is there any technology to replicate a table from public schema on the master server to a table on the slave server but not in the public schema. MASTER SERVER SLAVE SERVER pubic.table_name ----- replicate -----> schema1.table_name I tried slony-I but the problem that the table on the slave must in the public schema too.

I inherited a 5 node postgres cluster running postgres 8.4 and slony 1.2.21. We are in the process of migrating the application to all new code and have not wanted to do very little maintenance on the cluster. Yesterday we decided to take down two nodes out of the cluster which were not being used. We used the slonik script to DROP NODE for the two nodes in the cluster. This seemed to work correctly and we shut down the nodes today. However I noticed this morning that our master database where the collect writes is not replicating the changes to the rest of the servers. I have tried everything I can think of but it appears but nothing seems to work. When I run a query to collect the status I see that events are not being acknowleged since yesterday. The st_last_received has not changed at all. st_origin | st_received | st_last_event | st_last_event_ts | st_last_received | st_last_received_ts | st_last_received_event_ts | st_lag_num_events | now -----------+-------------+---------------+----------------------------+------------------+----------------------------+----------------------------+-------------------+------------------------------- 25 | 24 | 26196903 | 2016-11-29 17:39:06.859051 | 26187885 | 2016-11-29 12:51:45.396619 | 2016-11-28 11:11:48.909855 | 9018 | 2016-11-29 17:39:07.247598-05 25 | 27 | 26196903 | 2016-11-29 17:39:06.859051 | 26187885 | 2016-11-28 11:11:49.203193 | 2016-11-28 11:11:48.909855 | 9018 | 2016-11-29 17:39:07.247598-05 25 | 26 | 26196903 | 2016-11-29 17:39:06.859051 | 26187885 | 2016-11-28 11:11:50.253235 | 2016-11-28 11:11:48.909855 | 9018 | 2016-11-29 17:39:07.247598-05 I first restarted the slony daemons on all the nodes and have subsequently done this multiple times. I have set debugging to level 4 for debug logging and have combed through them without finding a single issue. I have looked through all the *.sl_* tables for anything that might tell me why it is not working. Our configuration is as follows for the important replication set. select * from _ads.sl_set; set_id | set_origin | set_locked | set_comment --------+------------+------------+----------------- 1 | 25 | | mgt tables select * from _ads.sl_subscribe ; sub_set | sub_provider | sub_receiver | sub_forward | sub_active ---------+--------------+--------------+-------------+------------ 1 | 25 | 26 | t | t 1 | 25 | 27 | t | t 2 | 25 | 27 | t | t 1 | 25 | 24 | t | t select * from _ads.sl_listen ; li_origin | li_provider | li_receiver -----------+-------------+------------- 24 | 24 | 25 26 | 26 | 25 27 | 27 | 25 27 | 25 | 26 26 | 25 | 27 27 | 25 | 24 24 | 25 | 27 24 | 25 | 26 26 | 25 | 24 26 | 24 | 25 27 | 26 | 25 26 | 27 | 25 24 | 26 | 25 27 | 24 | 25 24 | 27 | 25 25 | 25 | 24 25 | 25 | 26 25 | 25 | 27 Any advice assistance, or an idea on where to look would be greatly appreciated. I am in full on panic mode now.

I have multiple transaction PostgresQL 9.4 databases and a single publication read-only database. Is there any replication mechanism (asynchronous) to push every updated transaction from every database to the one publication database. Is there any work around Slony to accomplish this? Thanks in advance.

Is it actually possible to somehow use Slony with Amazon’s RDS service? I’m aware that there’s no such thing as a host that you can log into to make Slony’s configurations, but perhaps it can be done remotely from a side server or something like that?

I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, incompatible with 64-bit PostgreSQL. My OS in Windows 7 64-bit. Is there any easy way how to install it? Or do I have to build it from source? Or is there no way to do it except for move to 32-bit ?

What is the behavior of Slony waiting for an event after lock set command? Basically I am trying to do a switchover and it takes a lot of time. We are following these steps: 1. Lock Set 2. Wait for Event (timeout = 300) 3. Move Set (timeout = 300) 4. Wait for Event (timeout = 300) The alt perl script we have with Slony source has the following sequence: 1. Lock Set 2. Sync 3. Wait for Event 4. Move Set What should be the correct sequence of events and how can I make the switchover faster?

With PostgreSQL, I want to replicate a subset of all tables from Database A to B, but only once a day. Since I do not know what to search for: is there a technical term for such a scenario? Does e.g. Slony-I support this?

I try to start Slony replication. I have PostgreSQL 9.3.4, PgAdmin III 1.18.1 and Slony-I 2.2.0-1. I have set the path to Slony to D:\GIS\PostgreSQL\9.3\share where I have the Slony files. Whenever I try to create new Slony-I cluster, there is following message on the bottom of the window: Slony-I creation scripts not available; only joining possible. When looking for solution, I found a helpful maillist . Especially first post by Neel Patel was helpful: > Sorry for the late replay, we have checked and it is a bug in pgAdmin > because the name of the sql files got changed in new slony version ( > v2.2.0 ) which is used by the pgAdmin. > > Issue :- > > When we install the slony ( version less than 2.2.0 ) then it will > install below sql files in C:\Program Files\PostgreSQL\9.3\share > folder. > > slony1_base.sql slony1_base.v83.sql slony1_base.v84.sql > slony1_funcs.sql slony1_funcs.v83.sql slony1_funcs.v84.sql > > Now in New version of slony 2.2.0 onwards the name of the above sql > files got changed as below. > > slony1_base.2.2.0.sql slony1_base.v83.2.2.0.sql > slony1_base.v84.2.2.0.sql slony1_funcs.2.2.0.sql > slony1_funcs.v83.2.2.0.sql slony1_funcs.v84.2.2.0.sql > > As we are using above sql files names in pgAdmin to create the new > slony clusters so we need to change in pgAdmin also. Later in the discussion a patch fixing it appeared, but I don't know how to apply it - see my previous question . See that question for the ways I tried. Is there any way to solve this problem other than to install the patch? Help with installing the patch would be also appreciated, but I would prefer it to appear in the linked answer than here, this question should focus on other ways to solve it. EDIT: I tested different versions of PostgreSQL and Slony-I (Slony 2.0.7 on PostgreSQL 9.1 and Slony 2.1.3 on PostgreSQL 9.2) and also pgAdmin 1.16. The same problems occurs everywhere.

I have installed pgAdmin 1.18.1 but whenever I am trying to create a new cluster using Slony-I, it is always saying that it is missing some scripts. I have copied some .sql scripts from my Slony folder in linux to my pgAdmin folder in windows and set the options file for this folder. However, the problem remains. Does anyone have a solution for this? perhaps anyone can share the scripts please ? I am using PostgreSQL 9.3 on Ubuntu 12.04 and pgAdmin on windows.

I am using Slony to replicate a Postgres database. If my slony daemon exits for whatever reason, and doesn't start up hypothetically until 1 month later, will all the data that has accumulated in the master be replicated eventually to the slave? Of course, it might take a long time for that to happen, but will any data be lost as a result?

Trying to run the example slony config script in the documentation but getting the following error: postgres$ /tmp/slonik_example.sh :8: PGRES_FATAL_ERROR load '$libdir/slony1_funcs'; - ERROR: could not access file "$libdir/slony1_funcs": No such file or directory :8: Error: the extension for the Slony-I C functions cannot be loaded in database 'dbname=my_primary host=localhost user=warfish password=coalitions' Yet the LIBDIR variable is set correctly: postgres$ ./pg_config BINDIR = /opt/local/lib/postgresql90/bin DOCDIR = /opt/local/share/doc/postgresql HTMLDIR = /opt/local/share/doc/postgresql INCLUDEDIR = /opt/local/include/postgresql90 PKGINCLUDEDIR = /opt/local/include/postgresql90 INCLUDEDIR-SERVER = /opt/local/include/postgresql90/server LIBDIR = /opt/local/lib/postgresql90 And the lib is present: ls -l /opt/local/lib/postgresql90/slony1_funcs.so -rwxr-xr-x 1 root admin 34944 Feb 17 16:20 /opt/local/lib/postgresql90/slony1_funcs.so Script is as follows: cat /tmp/slonik_example.sh #!/bin/sh CLUSTERNAME=slony_example; /opt/local/lib/postgresql90/bin/slonik <<_EOF_ define CLUSTERNAME slony_example; cluster name = @CLUSTERNAME; node 1 admin conninfo = 'dbname=my_primary host=localhost user=user1 password=pw'; node 2 admin conninfo = 'dbname=my_rep host=localhost user=user1 password=pw'; #-- # init the first node. Its id MUST be 1. This creates the schema # _$CLUSTERNAME containing all replication system specific database # objects. #-- init cluster ( id=1, comment='Master Node'); #-- # Slony-I organizes tables into sets. The smallest unit a node can # subscribe is a set. The following commands create one set containing # all 4 pgbench tables. The master or origin of the set is node 1. #-- create set (id=1, origin=1, comment='All pgbench tables'); set add table (set id=1, origin=1, id=1, fully qualified name='public.pgbench_accounts', comment='accounts table'); set add table (set id=1, origin=1, id=2, fully qualified name='public.pgbench_branches', comment='branches table'); set add table (set id=1, origin=1, id=3, fully qualified name='public.pgbench_tellers', comment='tellers table'); set add table (set id=1, origin=1, id=4, fully qualified name='public.pgbench_history', comment='history table'); #-- # Create the second node (the slave) tell the 2 nodes how to connect to Slony-I 2.1.1 Documentation 10 / 163 # each other and how they should listen for events. #-- store node (id=2, comment = 'Slave node', event node=1); store path (server = 1, client = 2, conninfo='dbname=my_primary host=localhost user=user1 password=pw'); store path (server = 2, client = 1, conninfo='dbname=my_rep host=localhost user=user1 password=pw'); _EOF_ Read in the doc that likely the lib is not in the correct place or $libdir is not set correctly, but everything looks to be in place. Am I missing something else I am not aware of?

I have a master and multiple slave machines. All SQL operations on the master node should be sent to the slave nodes. But the tables on the slave nodes may also be written to locally. These local changes on the slave node should be reflected to the master node. For example let's say I have a master machine, lets call it "M", and two slave machines lets call them "A" and "B" All these machines have a table named test_table with a column named "id". I insert data to the test_table on M machine. (M)test_table |id| |1 | (A)test_table |id| (B)test_table |id| now this change is reflected to the slaves: (M)test_table |id| |1 | (A)test_table |id| |1 | (B)test_table |id| |1 | Now on slave A, I make a local change. (M)test_table |id| |1 | (A)test_table |id| |2 | (B)test_table |id| |1 | Now this change is reflected to the master server: (M)test_table |id| |2 | (A)test_table |id| |2 | (B)test_table |id| |1 | And then master server replicates this change to the slaves: (M)test_table |id| |2 | (A)test_table |id| |2 | (B)test_table |id| |2 | What is this kind of replication named? And how can I achieve this on postgresql?

There is a system with up to 256 nodes in db cluster. We need to replicate some data from one of them across to all others. But for every row to replicate there is a list of nodes where it must be replicated while on other nodes same row becomes orphan without one of parent table keys. To filter them out, or to prevent them from replicating needlessly, I'm trying to find a way to teach Slony to select nodes for replication however can't find such feature readily available. Has anyone tried it (customizing source code of its functions, etc.)? _id,_text,_nodes ---------------- 1 'one' {1,101} 2 'two' {2,102} 256 '...' {0,256} Row id=1 is then replicated to node 1 and 101, etc. Similar discussion https://dba.stackexchange.com/questions/20616/replicating-only-significant-records-to-specific-database however doesn't point in the right direction yet.

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 for a temporary Slony set containing only that view. I get rejected with ERROR: Slony-I: table "public"."name_of_view" has no primary key error. To my knowledge, views don't support primary keys. Does this mean there is no way, using the Slony admin scripts, to add a view to replication, and that I should just install it manually on all members of my replication cluster?

I am having a postgres database on my portable and on a server. I would like to keep those two in sync as easy as possible. I know about slony but I am not able to figure out: Does it work when the two servers are not always connected? - I may do changes on the db on my portable when it is not able to reach the other server. Does it work both ways? Can I update the slave and get the updateds pushed to the master? Does it sync changes in the db-structure? The database in question is a geographical database, and occasionally I import a new map as a new table, I am also often adding new view for data processing. If slony is not useable in my case, are there any other usable tools? I've looked at dblink, but that does neither seem to be what I need.

I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another. The second database is the master db in a Slony replication cluster. No matter what options I specify in pg_dump the changes are not being replicated. I’ve use a standard dump which uses COPY, a --inserts dump which uses INSERT INTO and a --insert-columns dump which adds column names to the INSERT INTO statements. If I edit the data in pgAdmin III using the view option for the table, that change is replicated just fine. What is the difference between editing the data that way in pgAdmin III and using psql to load a dump file?

**Context:** We run Slony 2.0 with Postgres 8.4 on two CentOS 6 servers--one master, and one slave. Our database is about 30GB in size, which isn't unusual, but we do have a couple of tables that are more than 5GB each. Recently, we needed to re-build our Slony cluster. I turned off Slony, restored identical database snapshots on the master and the slave, set up my slony.conf and slon_tools.conf, started the slons, ran slonik_init_cluster | slonik, then slonik_create_set 1 | slonik (we only have one replication set), and finally slonik_subscribe_set 1 2 | slonik. Everything looked good, and I was able to watch subscription progress in the logs. Then the server stopped responding. I rebooted it, and saw "Kernel panic - not syncing: Out of memory and no killable processes" after it had killed everything it could. **What I've tried:** First I blew away the database completely, re ran initdb, and then restored the identical snapshots again. Same kernel panic. Then I blew it away, uninstalled Postgres and Slony, and reinstalled them. I double-checked all of our memory-based settings in postgresql.conf, and they are all at stock/recommended levels (i.e. shared_buffers is at 1/4 of RAM etc etc). I ran a VACUUM ANALYZE FULL on the database before initializing the Slony cluster. Same result every time: kernel panic, out of memory. There's no chance of random/manual config changes having caused this: all of our Postgres and Slony configuration is managed by Puppet , and hasn't changed for months. **Question:** Why is this happening? Our database has grown fairly linearly over the past few months (at the beginning of the year it was about 23GB, now it's 30), and every other time I have had to re-initialize the Slony cluster on these same servers, it has worked fine.

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 not providing automatic failover for streaming replication. We can switch the slave to master using a trigger file but it is not manageable. So I'd like a solution with automatic failover and high availability. Different solutions are available: 1. Repmgr 2. Linux Heartbeat 3. Pgpool-II (for automatic failover only) 4. Any other tool in case you used. My question is which solution should be used?