postgresql - how to replicate database/tablespace with a shared iscsi volume

eantoranz · 11-01-2012, 02:49 PM

Hi!

I'm experimenting/documenting the process of setting up a cluster with pacemaker.

I have these two virtual machines sharing an iSCSI volume. I have set up a partition in that volume. I have formatted the partition and can mount it in both servers (manually, one at a time, not using pacemaker yet).

Next step would be to create the tablespace using that partition and then create the database using that separate tablespace. I can do it in one of the servers but then my question is: how can I make the other server start using the tablespace/database that were created on the first server? Just so that you have no doubts:
- I will stop postgres on the first server
- I will umount the partition
- I will mount the partition on the second server
- What's next so that postgres on the second server can see that tablespace/database?

Thanks in advance.

eantoranz · 11-01-2012, 03:33 PM

This is hackish but I think could work, what do you think?

On the first server, I create the tablespace and the database (using the tablespace).. leaving the database empty for the moment.

I shutdown postgres on the first server
I umount on the forst server
I mount on the second server
I delete everyting from the tablespace mountpoint
I create the tablespace on the second server
I create the database using the new tablespace.

Now, I shutdown postgres, umount on the second server
I mount and start postgres on the first server. It should "think" there were no changes on the tablespace and I should be able to switch to the second server as well, am I right?

eantoranz · 11-01-2012, 04:03 PM

Nope... that didn't work.

After I had recreated the empty database on the second server, I noticed that the directory names that were present at the root of that fs I set up for the tablespace were not the same:

When I just mounted the FS on the second server (before deleting everything so that I could recreate the tablespace/database):

Code:

$ sudo ls -l /var/lib/postgresql/sanos/
total 8
drwx------ 2 postgres postgres 4096 2012-11-01 16:04 16388
-rw------- 1 postgres postgres    4 2012-11-01 15:39 PG_VERSION

Then I deleted everything and proceeded to create the tablespace/database:

Code:

$ sudo ls -l /var/lib/postgresql/sanos/
total 8
drwx------ 2 postgres postgres 4096 2012-11-01 16:22 16386
-rw------- 1 postgres postgres    4 2012-11-01 16:22 PG_VERSION

16388 vs 16386. Anyway, I shutdown postgres on the second server, umounted. Mounted on the first server, started postgres on the first server.

Code:

$ sudo /etc/init.d/postgresql-8.4 start
 * Starting PostgreSQL 8.4 database server                                                                                                                                   [ OK ] 
$ sudo -u postgres psql sanos
psql: FATAL:  database "sanos" does not exist
DETAIL:  The database subdirectory "pg_tblspc/16387/16388" is missing.

End of story.

eantoranz · 11-01-2012, 04:16 PM

SO the only way to pull this off is by sharing data_directory (as set in postgresql.conf)?