Thank you for the quick reply. I very recently had some success by doing a complete purge of postgresql-9.5, reinstalling and then copying in the original data directory (or I guess I could have just pointed to it, but w/e). I did not expect this to work, as I thought it would just point to the table holding the roles
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? 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.kla...@aklaver.com> 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 >> r...@pfcta.com <mailto:r...@pfcta.com> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Robert McAlpine DevOps Engineer Perfecta Federal <http://www.perfectafederal.com/> 6506 Loisdale Road Springfield, VA 22150 O: 202.888.4949 ext 1005 C: 757.620.3503 r...@pfcta.com
