Re: Cannot create role, no default superuser role exists - Mailing list pgsql-general

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