Re: Cannot create role, no default superuser role exists

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(dot)klaver(at)aklaver(dot)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(at)pfcta(dot)com <mailto:rem(at)pfcta(dot)com>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)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
rem(at)pfcta(dot)com