PostGresQL - Macpeters/resume GitHub Wiki

PostgreSQL is a powerful, open source object-relational database system.

Installing

# Update homebrew
brew update
brew tap homebrew/services

# Install PostgreSQL specific version
brew install postgresql@11
brew services start postgresql@11

Update your .zshrc file's bin path.

if [ -d "/usr/local/opt/postgresql@11/bin" ] ; then
  export PATH="/usr/local/opt/postgresql@11/bin:$PATH"
fi

NOTE - PostgreSQL does not have a default root user. Instead it uses roles and associates your current user login with the postgres role. To connect to postgres use your current user name (i.e. whoami)

# load interactive shell
psql postgres

\q # to quit

Command Line

Here is a list of common postgres command line tools:

psql # sql commands
dropdb # drops a database
createdb # creates a database
pg_dump # for database backups
pg_restore # for database restores

Applications

Install some recommended client applications:

# Similar to Sequel Pro
brew cask install postico --force

# Official PostgreSQL Admin GUI
brew cask install pgadmin4 --force

Extensions

PostgreSQL has extension support to add extended features (i.e. crosstab). These extensions must be explicitly enabled on the database server by a super user role. Connect to the database server as the super user (i.e. $USER or 'rails' in AWS)

# Enable table functions on all new databases (i.e. crosstab)
psql -d template1 -c 'CREATE EXTENSION tablefunc;'

# Enable table functions on a specific database
psql -d [database] -c 'CREATE EXTENSION tablefunc;'

Troubleshooting

Reinstall/Upgrade/Start Postgres

Checks and Updates

brew update
brew doctor

only one postgres should be listed - and started
brew services list

check all versions installed with homebrew

brew list --versions | grep postgres

the correct version of postgres has a checkmark (currently 11)

brew search postgresql

Check the current version:

psql --version

Check the version of pg_upgrade to make sure it matches

pg_upgrade --version

Uninstall Unwanted Versions

brew uninstall [email protected] 
rm -R /usr/local/var/postgres

Now the Install

brew install postgresql@11
brew link postgresql@11 --force
brew services start postgresql@11
brew postinstall postgresql@11

Errors

Installing postgresql

FATAL role: “username” doesn’t exist

createuser -u postgres

FATAL role: “Postgres” doesn’t exist

reinstall postgres (see above)

initdb: error: directory "/usr/local/var/postgres" exists but is not empty

rm -R /usr/local/var/postgres
brew postinstall postgresql
brew services start postgresql

—————

Running pg_restore

“AS Integer’ This was introduced in PSQL@10 - upgrade postgres to get rid of it

pg_restore: from TOC entry 1415; 1259 146082050 SEQUENCE dim_sr_offering_category_features_id_seq webapp_user pg_restore: error: could not execute query: ERROR:  syntax error at or near "AS" LINE 2:     AS integer

pg_restore: error: could not execute query: ERROR:  relation "public.dim_sr_offering_category_features_id_seq" does not exist

Incorrect version of postgres - reinstall postgres and/or update using above instructions

—————————

Running Migrate

_dyld: lazy symbol binding failed: Symbol not found: PQresultMemorySize Referenced from: /Users/mpeters/.rbenv/versions/2.6.5/lib/ruby/gems/2.6.0/gems/pg-1.2.3/lib/pg_ext.bundle Expected in: /usr/local/lib/libpq.5.dylib

gem uninstall pg
gem install pg