Thank you for the quick reply.
But to answer your questions anyways:
Did you install postgres-xc over your existing instance?
I think that is probably what happened. The reason I installed it is that I tried to run the command 'pg_ctl' and my box and couldn't find it. I had not used pg_ctl before, so I assumed it was a utility package, and installed it via postgres-xc.
Which data directory, the postgres-xc one or the original Postgres one?
The original.
Installed from a package or source?
From a package, specifically apt-get install postgresql-9.5
The dump was from a pre-xc version of Postgres?
From a package, specifically apt-get install postgresql-9.5
The dump was from a pre-xc version of Postgres?
No, my postgres version has not changed recently
before or after the reinstall?
After. I wanted to see if apt-get remove and then apt-get install would be enough of a jolt to fix the issue, as I didn't yet want to try apt-get purge, which would remove all my data.
On Thu, Mar 10, 2016 at 7:44 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 03/10/2016 04:11 PM, Robert McAlpine wrote:
Postgresql 9.5, Ubuntu 14.04.
I broke my ability to access postgres after attemping to install
postgres-xc (ironic, since I installed that to use pg_ctl to reload my
pg_hba.conf without restarting the entirety of postgres).
That is available with the stock Postgres, unless I am missing something:
http://www.postgresql.org/docs/9.5/interactive/auth-pg-hba-conf.html
"The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file."
Did you install postgres-xc over your existing instance?
After doing so I can no longer access my databases via psql or any other
utilities, getting the error:
FATAL: role "postgres" does not exist
Unfortunately the same error gets thrown if I attempt to use 'createuser'.
The postgres user and role were the only ones able to access postgresql
server, so altering the permissions in my pg_hba.conf also got me nowhere.
I made a complete copy of the data directory, took a snapshot (it is on
Which data directory, the postgres-xc one or the original Postgres one?
a vm), then purged postgresql-9.5 and reinstalled, restoring to a dump
that is a few days old. I thought about copying all the files from the
Installed from a package or source?
The dump was from a pre-xc version of Postgres?
It would seem to me if you reinstalled in default manner you would have a postgres user available. So where did you get:
FATAL: role "postgres" does not exist
before or after the reinstall?
data directory (except for the roles table file) into a fresh install
with a correct default postgres role, but my gut tells me that screwing
around with those files is doomed to fail.
Yeah, I would hold off doing that until it is clearer what is going on.
I would appreciate any help or thoughts on how to recover access to the
data.
--
Robert McAlpine
rem@xxxxxxxxx <mailto:rem@xxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
Robert McAlpine
