Thread: Cannot create role, no default superuser role exists
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).
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 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 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.
I would appreciate any help or thoughts on how to recover access to the data.
-- Robert McAlpine
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@pfcta.com <mailto:rem@pfcta.com> -- Adrian Klaver adrian.klaver@aklaver.com
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@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
rem@pfcta.com <mailto:rem@pfcta.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
Robert McAlpine
For future reference you can start the postmaster in single user mode and create a new postgres account or grant the current one superuser access if this happens again. Just google "Postgresql single user mode".
On 03/10/2016 05:07 PM, Robert McAlpine wrote: > 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 Just to be clear Postgres-xc != Postgres. Postgres-xc is a fork of the community Postgres. So in the below it is important to be clear when you are talking about Postgres vs Postgres-xc. To sort this out is going to require a step by step: 1) When did you do the apt-get install postgresql-9.5 2) When and how did install postgres-xc? 3) Do you have more then one Postgres/xc data directory. I ask because I suspect in step 2 that it may have been installed somewhere other then the community Postgres data directory. 4) What does ps ax|grep post give you? > > 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.klaver@aklaver.com <mailto:adrian.klaver@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 > rem@pfcta.com <mailto:rem@pfcta.com> <mailto:rem@pfcta.com > <mailto:rem@pfcta.com>> > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > Robert McAlpine > DevOps Engineer > Perfecta Federal <http://www.perfectafederal.com/> > 6506 Loisdale Road > Springfield, VA 22150 > O: 202.888.4949 ext 1005 <tel:202.888.4949%20ext%201005> > C: 757.620.3503 <tel:757.620.3503> > rem@pfcta.com <mailto:rem@pfcta.com> -- Adrian Klaver adrian.klaver@aklaver.com