I'm pretty new to databases in general (about two weeks), but I'm learning fast. If I'm using the wrong terminology anywhere, let me know in the comments. Bear with me. :)
The company I work for is using PostgreSQL 9.1 (with pgAdmin III) to manage their data. The scenario is this. Say we have a master database M. Only M will be receiving direct submissions of data. However, on a regular basis (or even a push-notification basis?), another database, T, needs to pull the data from M such that specific tables of M are replicated in T. As far as this problem is concerned, this is one-way communication.
The (slight) catch: T is not read-only. Changes in T still need to be persistent to T and T only. So, this problem is not strictly replication, but I believed it was a start. (In my mind, T could replicate M but not push its own changes to M, thus preserving integrity.) If there is a better way to go about this, please don't hesitate to comment some links or keywords. :)
I need to set up Master-Slave replication of a subset of tables in the Master database. That is, specific tables from the master must always be pushed onto the slave, but the slave is free to make its own local changes.
I am using pgAdmin III with Slony-I. On the master, I've set up a Slony cluster and added my admin node, my master node, and my slave node. I believe I correctly made paths and listens for open communication between the master and the slave. I'll post their configuration as screenshots at the end.
I've created a table in the
postgres database called
(For simplicity, I'm using the defaults username/password/databases for this research.)
\c postgres create table replicateme ( product_no integer PRIMARY KEY, name text price numeric );
I created a new replication set and
created a new replicated table,
At this point, after refreshing, I do not see the replication set show up on the pgAdmin instance on the slave node. This is odd because the nodes themselves do show up. If I connect to the master database 'normally,' the set (and table) show up, obviously. From the requirements though, I don't believe I'll be able to connect directly to the master database.
Thinking 'Oh, it's because I'm not subscribed,' I went on to create the subscription. I was only able to create it on the Master database though. Even after creation, I was left depressingly data-less.
Is there an extra step I'm missing? I'm hardly CLI averse, but pgAdmin/Slony was advertised to the development team as completely workable through the GUI. I'm getting the feeling it's not, but I cannot find any documentation that makes it clear where pgAdmin stops and the terminal begins. What is left to be done?