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

- Linux - Server (http://www.linuxquestions.org/questions/linux-server-73/)

- - postgresql - how to replicate database/tablespace with a shared iscsi volume (http://www.linuxquestions.org/questions/linux-server-73/postgresql-how-to-replicate-database-tablespace-with-a-shared-iscsi-volume-4175435127/)

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

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.

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?

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.

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