10

I'm new to managing servers, especially postgresql on Ubuntu Server, so bear with me.

I'm trying to synchronize between two databases on two different computers, but I'm not sure what I did wrong, since whatever I tried to do, it seems to have configured the servers into read-only mode.

For example, if I wanted to create a role, I would get:

ERROR: cannot execute CREATE ROLE in a read-only transaction

Or if I want to create a table, I would get an error message:

ERROR: cannot execute CREATE TABLE in a read-only transaction.

I have no idea what to do here, so advice on how to resolve this issue is greatly appreciated.

I'm running PostgreSQL version 9.1 on Ubuntu Server 12.04 for by the way.

hdr
  • 163
  • 1
  • 2
  • 9
  • 2
    Is the server a read-replica? – Craig Ringer Sep 24 '14 at 02:16
  • I'm not sure, actually. I was following this guide [link]https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps[/link] on how to set it up, and somehow I messed up along the way. Is there a way to find out? – hdr Sep 24 '14 at 02:25
  • 4
    `SELECT pg_is_in_recovery()` – Craig Ringer Sep 24 '14 at 02:32
  • I ran that comment in psql and got nothing in response. – hdr Sep 24 '14 at 02:43
  • Did you end it with a semicolon? All SQL commands are terminated with semicolons. (I know I didn't write one above, I tend to assume). See http://stackoverflow.com/q/12472026/398670 – Craig Ringer Sep 24 '14 at 02:44
  • Oh, sorry about that. Didn't realize that. I ran it again, and got: 'ERROR: syntax error at or near "SELECT" LINE 2: SELECT pg_is_in_recovery();' with an arrow pointing at the s in SELECT. – hdr Sep 24 '14 at 02:47
  • You repeated it, so you ran `SELECT pg_is_in_recovery() SELECT pg_is_in_recovery();`. Just run `SELECT pg_is_in_recovery();`. – Craig Ringer Sep 24 '14 at 02:53
  • My mistake. I ran the command again, and got this: 'pg_is_in_recovery (separated by a line) t (1 row)' – hdr Sep 24 '14 at 02:59

2 Answers2

16

Since SELECT pg_is_in_recovery() is true you're connected to a read-only replica server in hot_standby mode. The replica configuration is in recovery.conf.

You can't make it read/write except by promoting it to a master, at which point it will stop getting new changes from the old master server. See the PostgreSQL documentation on replication.

First step is to check whether there is a 'recovery.conf' file in the data directory. If it exists, if you are sure you are on master (not slave) server, rename that file to 'recover.conf.backup'. Then, re-start postgresql server. It should allow you to write new records now.

Andrew Schulman
  • 8,811
  • 21
  • 32
  • 47
Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
  • The problem is is this is the master server, thus I was unable to do anything, let alone replicate. – hdr Sep 24 '14 at 03:33
  • @hdr Then I'd say you accidentally created `recovery.conf` on the master server instead of the standby. – Craig Ringer Sep 24 '14 at 05:53
  • Seems like it. Would removing it solve the issue? – hdr Sep 24 '14 at 06:12
  • @hdr Well, it'd make the node read/write. I can't say if that's the right thing to do without knowing all the prior steps you did and where you went off track from the documentation. – Craig Ringer Sep 24 '14 at 07:32
  • Well, it was a bit of a drastic measure, but I nuked the whole thing and reinstalled from scratch, and it seems to work fine now. Thanks for your advice, at least now I know what went wrong and that allowed me to prevent it from happening again. – hdr Sep 24 '14 at 16:27
  • in Heroku, will we be able to do this? We won't have access to the Postgres server machine. – Surya Aug 29 '19 at 09:19
1

if it's an RDS instance cluster, check your endpoint and use the Writer instance endpoint.

Aditya Y
  • 131
  • 4