CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!
This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.
What you should do is follow Chris James's answer:
sudo -u postgres psql postgres
# \password postgres
Enter new password:
To explain it a little bit. There are usually two default ways to login to PostgreSQL server:
By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql
. Note that sudo -u
does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).
So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.
Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.
In this case, where the -P flag still results in errors, the database is almost certainly beyond (reasonable) recovery.
This:
postgres -O -P -D /data/dir -c log_error_verbosity=verbose dbname
yields additional information. In this instance it meant corruption beyond the pg_class table.
More info on the pgsql-admin list.
Best Answer
Personally, I'd just remount whatever you've stuck at
/database
onto/var/lib/postgresql
, because you're going to confuse the living heck out of anyone who has to deal with this frankenmachine in the future. That being said, if you really want to move it, there's two ways forward:Make
/var/lib/postgresql
a symlink to/database
. Keeps the filesystem structure intact, at the cost of a symlink (which give some people the heebie-jeebies).The other way is a bit more involved, but arguably "more correct". In this case, install just the
postgresql-common
package, before you install any of the other postgresql-related packages. Then, edit/etc/postgresql-common/createcluster.conf
, and change the line that setsdata_directory
so that it readsdata_directory = '/database/%v/%c'
. So you'll need to uncomment it, then edit the value. (While you're in there, you can also changeinitdb_options
to specify a different character encoding and locale, if you want, and setlog_line_prefix
to something more readable...)Then install the
postgresql-9.4
package. It'll pick up the custom value fordata_directory
and create the new cluster in there, instead of in the default location.