Search code examples
postgresqlrecovery

How to exit out of database recovery mode (currently locked in read-only mode)

A slave database was set up some time ago for the purpose of backing up or replicating a remote database. However I can no longer write to the database using a Delphi based ETL (the ETL works for another database pair, but to date has never been used for this particular pair). The replication database was setup by somebody else who has since left the company. I am reasonably sure this has been setup as a replication database, however the employee who has since left told me that replication never worked for unrelated reasons. Using the ETL we can (using SQL queries) read from the one database, and write back to the replication database, Or should be able to, as it is currently read only.

I have tried:

  1. Maintenance such as VACUUM
  2. Attempt to drop tables and the entire database
  3. Restore a full backup from the master database

None of these work, and I am told the database is read-only.

I have looked at postgresql.conf and see that hot_standby is checked, so I think (but am not 100% certain) that the database is in some sort of replication mode (I've never touched replication as supported by Postgres, so I wouldn't know).

I have checked permissions in pg_hba.conf and see there are some credentials in there for replication. I am not sure whether this activates "replication mode" for the database, or simply means these credentials are for replication only.

I have been through months worth of log files (This has not been working since our IT department upgraded the entire network about 5 months ago). I see the log file contents seen below, repeated over and over with nothing else for months. Note the IP address shown below is listed in the pg_hba.conf file, so credentials are valid.

The database is in recovery mode, as I have found by using:

select pg_is_in_recovery();

This explains to me why it's read only, but why can I not restore databases, or just simply dump the entire database and start again (it's a backup so losing/restoring it is not an issue)?

I was tempted to try modifying the recovery.conf file (which exists) but I read/believe that once recovery has been initiated (which in my case it has) modifying the file will have no effect.

I'm using a legacy version of Postgres: 9.2.9

Any help here would be greatly appreciated, as I have been working solidly on this for more than a day now.

Log File entry (sample):

FATAL:  could not connect to the primary server:
FATAL:  no pg_hba.conf entry for replication connection from host "192.168.20.2", user "postgres", SSL off

FATAL:  could not connect to the primary server: server closed the connection unexpectedly
        This probably means the server terminated abnormally before or while processing the request.

A couple of options would work for me:

  1. Convert the database from being a read-only replication database, to a standard read/write database or
  2. Dump/drop the entire database so I can create a new one with write capabilities.
Solution

  • It looks like the two database clusters have been set up for replication, but configuration changes on one of the machines broke the replication (changed pg_hba.conf on the primary, changed IP addresses, …).

    Here is the way to your desired solutions:

    1. Bringing the standby out of recovery mode: Run

      /path/to/pg_ctl promote -D /path/to/data/directory

      on the standby as operating system user postgres.

    2. Nuking the standby: Remove the data directory on the standby with rm -rf (or the equivalent on your operating system). Kill all PostgreSQL processes.

      Then use initdb to create a new database cluster in the same location.