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@xxxxxxxxxxx <mailto: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> <mailto:rem@xxxxxxxxx
<mailto:rem@xxxxxxxxx>>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
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@xxxxxxxxx <mailto:rem@xxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
