Versions Compared

Old Version 1

changes.mady.by.user E. Lynette Rayle

Saved on

compared with

New Version Current

changes.mady.by.user E. Lynette Rayle

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

← ALL HOW TOs


Expand
titleTable of Contents

Table of Contents


Postgres Service


Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac


Install Postgres...


Code Block
languagenone
brew install postgresql




Start/Restart/Stop Postgres...


Prereq: Install Homebrew services (one time only)


Code Block
languagenone
brew tap homebrew/services




Running postgres service


Code Block
languagenone
brew services start postgresql
brew services restart postgresql
brew services stop postgresql


NOTE: Restarts automatically after when computer is restarted.




Check version...


Code Block
languagenone
psql -V




Using with Rails...


Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac


Install postgres gem


Code Block
languagenone
rails new -d postgresql


OR


Code Block
langaugenoen
gem install pg -- --with-pg-config=/usr/local/bin/pg_config


OR


edit Gemfile and add gem


Code Block
languageruby
gem 'pg', '~> 0.18'




Typical database.yml configuration


Code Block
languagenone
default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: myapp_dev
  username: your_username_for_postgres
  password: your_password_for_postgres

test:
  <<: *default
  database: myapp_test
  username: your_test_username_for_postgres
  password: your_test_password_for_postgres




Postgres command line


Reference: http://blog.jasonmeridth.com/posts/postgresql-command-line-cheat-sheet/


Access the command line


Code Block
$ psql postgres
postgres=#


Exit command line


Code Block
\q   # backslash q




List databases


Code Block
\l  # backslash l




List roles (aka users)


Code Block
\du




Create role (aka user)


Code Block
CREATE ROLE new_role_name WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;




Create database


Code Block
CREATE DATABASE new_database_name WITH OWNER a_role_name ENCODING 'UTF8';




Grant role (user) privileges


Code Block
GRANT ALL PRIVILEGES ON DATABASE a_db_name TO a_role_name;


Ref: https://www.postgresql.org/docs/9.1/static/sql-grant.html




Change owners of a database


Code Block
ALTER DATABASE a_db_name OWNER TO new_owner;


Ref: https://www.postgresql.org/docs/9.1/static/sql-alterdatabase.html


Change user to superuser


Code Block
languagenone
ALTER USER a_role_name WITH SUPERUSER;




Drop database


Code Block
DROP DATABASE a_db_name;




Connect to database


Code Block
\c a_db_name




List tables in connected database


Code Block
\dt




List columns in table in connected database


Code Block
\d a_table_name




View contents of a table (aka execute queries)


Code Block
languagenone
select * from a_table_name;




Delete a row from a table


Code Block
languagenone
delete from a_table_name where id = 1;   # deletes row with id=1  (can use any where clause to identify rows)
delete from a_table_name;                # deletes all rows