← ALL HOW TOs
Postgres Service
Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac
Install Postgres...
Code Block |
---|
|
brew install postgresql |
Start/Restart/Stop Postgres...
Prereq: Install Homebrew services (one time only)
Code Block |
---|
|
brew tap homebrew/services |
Running postgres service
Code Block |
---|
|
brew services start postgresql
brew services restart postgresql
brew services stop postgresql |
NOTE: Restarts automatically after when computer is restarted.
Check version...
Using with Rails...
Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac
Install postgres gem
Code Block |
---|
|
rails new -d postgresql |
OR
Code Block |
---|
|
gem install pg -- --with-pg-config=/usr/local/bin/pg_config |
OR
edit Gemfile and add gem
Code Block |
---|
|
gem 'pg', '~> 0.18' |
Typical database.yml configuration
Code Block |
---|
|
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)
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 |
---|
|
ALTER USER a_role_name WITH SUPERUSER; |
Drop database
Code Block |
---|
DROP DATABASE a_db_name; |
Connect to database
List tables in connected database
List columns in table in connected database
Code Block |
---|
\d a_table_name |
View contents of a table (aka execute queries)
Code Block |
---|
|
select * from a_table_name; |
Delete a row from a table
Code Block |
---|
|
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 |