Home / server / Questions / 684070
Accepted
jishi
Asked: 2015-04-21 06:28:22 +0800 CST

Changing tablespace on table with streaming replication

  • 772

I've been searching for answers for a while know but can't find any reliable source.

We have 2 postgres servers that replicates using streaming replication. I now need to move some tables to a different tablespace (residing on a different volume), which would be a no-brainer but I have no idea how this will propagate to the slave.

I need to do the same change on the slave, because it has an identical disk setup, but I'm not sure if I need to create the tablespace on the slave, beforehand (and if handled directly by the replication) or if I need to run the same alter statement on the slave (but it is readonly, and I don't know if that is allowed).

OR, do I need to make a new base backup and set up the replication from scratch after this change?

postgresql

1 Answers

  1. Best Answer

    At least for version 9.4, an identical filesystem path needs to exist on the standby(s). From http://www.postgresql.org/docs/9.4/static/warm-standby.html#STANDBY-PLANNING:

    "In particular, the path names associated with tablespaces will be passed across unmodified, so both primary and standby servers must have the same mount paths for tablespaces if that feature is used. Keep in mind that if CREATE TABLESPACE is executed on the primary, any new mount point needed for it must be created on the primary and all standby servers before the command is executed."

    • 4