PostgreSQL – Changing IP Address of Master Server

postgresqlpostgresql-9.1

We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated and for the sake of safety I'd rather not terminate it before the new one is bedded in).

Aside from the IP changes required in the pg_hba.conf, are there any other points I need to be mindful of?

I'm currently going through the slave and checking for instances of the primary's IP address and it's showing up in some files in $POSTGRES_DATA/main/base/ which is why I'm asking.

Postgres 9.1 on Ubuntu 12.04

Thanks

Best Answer

There are several places you'd want to change the IP address for a PostgreSQL primary server. Most of them are actually outside of PostgreSQL itself or its configuration, though.

You already mentioned the pg_hba.conf so that doesn't need to be mentioned.

  1. Tables that you have inet types for, or other columns you store IP addresses in. If you found IP addresses in $PGDATA/base via a grep, then that probably means that you have IP address entries in your tables, and looking at the information_schema.columns table for those would be most helpful.

    Really, needing to touch anything inside of a PostgreSQL data directory outside of .conf files is not recommended at all.

  2. postgresql.conf listen_addresses entries.

  3. Any recovery.conf files that have the primary's IP address referenced for streaming replication.

  4. IP addresses listed in pgbouncer or pgpool-II configurations.

  5. IPTables or other firewall rules on your servers.

  6. Application configurations.

  7. Remote backup scripts.

These are just highlights.

A comprehensive list will require you going patiently through everything in your setup. Creating a good checklist of what needs to be changed in the event of a failover (and keeping it up to date!) will help you immensely in the future, and improve your disaster recovery and high-availability readiness.

Hope that helps. =)

Related Question