postgresql with multiple units, hook failures with postgresql-psql charm

[James Page]

Access is granted to the postgresql-psql charm, but the second unit in the postgresql charm has hook execution errors. Looks like the hot standby unit also tries to create the access in the database, which fails.

juju status output:

  postgresql:
    charm: local:precise/postgresql-0
    exposed: false
    relations:
      db:
      - psql
      db-admin:
      - psql
      replication:
      - postgresql
    units:
      postgresql/0:
        agent-state: started
        machine: 37
        public-address: 10.5.0.40
      postgresql/1:
        agent-state: started
        machine: 38
        public-address: 10.5.0.48
        relation-errors:
          db:
          - psql
          db-admin:
          - psql
  psql:
    charm: cs:precise/postgresql-psql-3
    relations:
      db:
      - postgresql
      db-admin:
      - postgresql
    units:
      psql/0:
        agent-state: started
        machine: 39
        public-address: 10.5.0.43
        relation-errors:
          db:

charm log:

2013-06-21 08:54:11,631: unit.relation.lifecycle@DEBUG: Executing hook db-admin-relation-joined
2013-06-21 08:54:12,001: unit.hook.api@INFO: Running db-relation-joined hook
2013-06-21 08:54:12,075: hook.output@INFO: Traceback (most recent call last):

2013-06-21 08:54:12,076: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined", line 1993, in <module>

2013-06-21 08:54:12,076: hook.output@INFO:
2013-06-21 08:54:12,076: hook.output@INFO: raise SystemExit(main())

2013-06-21 08:54:12,077: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined", line 1932, in main

2013-06-21 08:54:12,077: hook.output@INFO:
2013-06-21 08:54:12,077: hook.output@INFO: db_relation_joined_changed(user, database, []) # No roles yet.

2013-06-21 08:54:12,078: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined", line 1267, in db_relation_joined_changed

2013-06-21 08:54:12,078: hook.output@INFO:
2013-06-21 08:54:12,079: hook.output@INFO: password = create_user(user)

2013-06-21 08:54:12,079: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined", line 1192, in create_user

2013-06-21 08:54:12,079: hook.output@INFO:
2013-06-21 08:54:12,080: hook.output@INFO: run_sql_as_postgres(sql, AsIs(quote_identifier(user)), password)

2013-06-21 08:54:12,081: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined", line 833, in run_sql_as_postgres
    cur.execute(sql, parameters)
psycopg2.InternalError: cannot execute CREATE ROLE in a read-only transaction

2013-06-21 08:54:12,091: hook.output@DEBUG: hook db-relation-joined exited, exit code Traceback (most recent call last):
Failure: juju.errors.CharmInvocationError: Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined': exit code 1.
.
2013-06-21 08:54:12,091: hook.executor@DEBUG: Hook error: /var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined': exit code 1.
2013-06-21 08:54:12,091: unit.relation.lifecycle@WARNING: Error in db-relation-joined hook: Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined': exit code 1.
2013-06-21 08:54:12,091: unit.relation.lifecycle@INFO: Invoked error handler for db-relation-joined hook
2013-06-21 08:54:12,093: hook.executor@DEBUG: Running hook: /var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined
2013-06-21 08:54:12,100: statemachine@DEBUG: relationworkflowstate: transition error (up -> error) {'error_message': "Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined': exit code 1.", 'change_type': 'joined'}
2013-06-21 08:54:12,100: statemachine@DEBUG: relationworkflowstate: execute action do_error
2013-06-21 08:54:12,101: hook.scheduler@DEBUG: stopping
2013-06-21 08:54:12,101: unit.relation.lifecycle@DEBUG: stopped relation:db lifecycle
2013-06-21 08:54:12,112: statemachine@DEBUG: relationworkflowstate: transition complete error (state error) {'error_message': "Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-relation-joined': exit code 1.", 'change_type': 'joined'}
2013-06-21 08:54:12,113: hook.scheduler@DEBUG: hook error, stopping scheduler execution
2013-06-21 08:54:12,113: hook.scheduler@INFO: stopped
2013-06-21 08:54:12,491: unit.hook.api@INFO: Running db-admin-relation-joined hook
2013-06-21 08:54:12,550: hook.output@INFO: Traceback (most recent call last):

2013-06-21 08:54:12,550: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined", line 1993, in <module>

2013-06-21 08:54:12,551: hook.output@INFO:
2013-06-21 08:54:12,551: hook.output@INFO: raise SystemExit(main())

2013-06-21 08:54:12,551: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined", line 1956, in main

2013-06-21 08:54:12,552: hook.output@INFO:
2013-06-21 08:54:12,552: hook.output@INFO: db_admin_relation_joined_changed(user, 'all')

2013-06-21 08:54:12,552: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined", line 1289, in db_admin_relation_joined_changed

2013-06-21 08:54:12,553: hook.output@INFO:
2013-06-21 08:54:12,553: hook.output@INFO: password = create_user(user, admin=True)

2013-06-21 08:54:12,554: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined", line 1192, in create_user

2013-06-21 08:54:12,554: hook.output@INFO:
2013-06-21 08:54:12,554: hook.output@INFO: run_sql_as_postgres(sql, AsIs(quote_identifier(user)), password)

2013-06-21 08:54:12,556: hook.output@INFO: File "/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined", line 833, in run_sql_as_postgres
    cur.execute(sql, parameters)
psycopg2.InternalError: cannot execute CREATE ROLE in a read-only transaction

2013-06-21 08:54:12,566: hook.output@DEBUG: hook db-admin-relation-joined exited, exit code Traceback (most recent call last):
Failure: juju.errors.CharmInvocationError: Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined': exit code 1.
.
2013-06-21 08:54:12,567: hook.executor@DEBUG: Hook error: /var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined Error processing '/var/lib/juju/units/postgresql-1/charm/hooks/db-admin-relation-joined': exit code 1.

[Stuart Bishop (stub)]

I'm reworking replication at the moment with a design that should work reliably rather than when hooks happen to be run in the right order.