All About PG

Errors

1. Not accepting connections

#<PG::ConnectionBad: could not connect to server: Connection refused
12:29:44 web.1    |     Is the server running on host "localhost" (::1) and accepting
12:29:44 web.1    |     TCP/IP connections on port 5432?

Remedy – verify pg is running, try to start pg

If that fails and gives you this error…

$ pg_ctl -D /usr/local/var/postgres start
pg_ctl: another server might be running; trying to start server anyway
server starting
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.6.1.

Fix for that

$ rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8

https://github.com/Homebrew/legacy-homebrew/issues/35240

General Debugging Tools

verify postgres running?

$ pg_ctl -D /usr/local/var/postgres status

are pg dependencies installed?

$ brew info postgres

install dependencies by upgrading postgres to latest version

$ brew upgrade postgres

Start postgres locally

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Stop postgres locally

$ pg_ctl -D /usr/local/var/postgres stop -s -m fast

Local PG Version

$ psql --version

Server PG Version, Row Limits, Row Counts

$ heroku pg:info

Version from within psql

$ psql
SELECT version();

Open PG on Heroku

$ heroku pg:psql

Close PG on Heroku

\q + Enter

Find Row Count per Table

$ heroku pg:psql
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Things I might want to eventually remember how to do

Open PG without rails

$ psql

That opens up a PG instance.

Find all PG roles/users

\du

Find all databases

\list

Quit psql

ctrl + z

Post Content