PostgreSQL setting

Quick notes for Postgresql

When you upgrade your PostgreSQL

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.5.1.
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Backup the old DB store and create a new one.

mv /usr/local/var/postgres /usr/local/var/postgresOLD
initdb /usr/local/var/postgres

If you want to start the DB on boot

cp /usr/local/Cellar/postgresql/<VERSION>/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
eg: cp /usr/local/Cellar/postgresql/9.5.1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents

Dump database

pg_dump -h localhost -U postgres -f testABC.dump TestABC_production -Fc

Restore database

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d TestABC_production ./testABC.dump

PSQL configuration file location /usr/local/var/postgres

CREATE USER postgres WITH PASSWORD '<YOUR_PASSWORD>';
ALTER USER postgres WITH SUPERUSER;

Login postgresql on ubuntu and change password

sudo su - postgres ; psql
ALTER USER "postgres" WITH PASSWORD 'TestABC123';
postgres=# ALTER USER "postgres" WITH PASSWORD 'TestABC123';
ALTER ROLE
postgres=# \q

md5 peer authentication 問題

you need to change the auth method from peer to md5 to enable Rails can works.

cd /etc/postgresql/9.5/main
sudo vim pg_hba.conf
sudo /etc/init.d/postgresql restart

http://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge