Postgres

Postgres.app

  • If installed PG some other way (like homebrew), uninstall: http://daniel.fone.net.nz/blog/2014/12/01/fixing-connection-errors-after-upgrading-postgres/
  • Install from http://postgresapp.com/
  • To get command-line access, add export PATH="/Applications/Postgres.app/Contents/Versions/9.4/bin:$PATH" to .bash_profile
  • gem install pg -- --with-pg-config=/Applications/Postgres.app/Contents/Versions/latest/bin/pg_config
  • Don’t need to create any users
  • Just remove username/password from DB config in Rails, then run bin/rake db:create:all and it will create the DBs
  • Connect with any Postgres client using username of your Mac username, no password

Postgres with Homebrew

brew install postgresql
brew services start postgresql
brew services list
psql

If you can’t connect, check the output of brew services list, look at the .plist file, check the ProgramArguments, run that command, and see the error it gives you

createuser shawsome
createdb -Oshawsome shawsome_development
createdb -Oshawsome shawsome_test

ALTER USER "myuser" WITH SUPERUSER; -- to create databases
ALTER USER "myuser" WITH PASSWORD 'mypassword';

(for some reason the double vs single quotes matter)

http://blog.willj.net/2011/05/31/setting-up-postgresql-for-ruby-on-rails-development-on-os-x/

\du - list users \l - list databases \connect mydbname - switch to db \dt - list tables \q - quit

psql -h host -p port -U username -d database pg_dump dbname > outfile psql dbname < outfile

Run as postgres user on Ubuntu:

sudo -i -u postgres [psql or pg_dump etc]

SUPERUSER

Seems to not be granted by default

  • Stop service: sudo service postgresql stop
  • Switch to postgres user: sudo su - postgres
  • Run single user mode: /usr/lib/postgresql/9.5/bin/postgres –single -D /etc/postgresql/9.5/main/
  • ALTER USER postgres WITH SUPERUSER;
  • If you want to confirm added, select usesuper from pg_user where usename = CURRENT_USER;
  • Ctrl-D to quit
  • Start service: sudo service postgresql start
  • Run migration that requires SUPERUSER
  • Remove superuser
  • ALTER USER postgres WITH NOSUPERUSER;