Postgres Service
Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac
Install Postgres...
brew install postgresql
Start/Restart/Stop Postgres...
Prereq: Install Homebrew services (one time only)
brew tap homebrew/services
Running postgres service
brew services start postgresql brew services restart postgresql brew services stop postgresql
NOTE: Restarts automatically after when computer is restarted.
Check version...
psql -V
Using with Rails...
Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac
Install postgres gem
rails new -d postgresql
OR
gem install pg -- --with-pg-config=/usr/local/bin/pg_config
OR
edit Gemfile and add gem
gem 'pg', '~> 0.18'
Typical database.yml configuration
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
$ psql postgres postgres=#
Exit command line
\q # backslash q
List databases
\l # backslash l
List roles (aka users)
\du
Create role (aka user)
CREATE ROLE new_role_name WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;
Create database
CREATE DATABASE new_database_name WITH OWNER a_role_name ENCODING 'UTF8';
Grant role (user) privileges
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
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
ALTER USER a_role_name WITH SUPERUSER;
Drop database
DROP DATABASE a_db_name;
Connect to database
\c a_db_name
List tables in connected database
\dt
List columns in table in connected database
\d a_table_name
View contents of a table (aka execute queries)
select * from a_table_name;
Delete a row from a table
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
