Django with Postgres on Mac

1. Create virtual env following this post
2. Install django
(my_env) $ pip3 install psycopg2
(my_env) $ pip3 install django
3. Create django project
(my_env) $ django-admin startproject sample_project
(my_env) $ python3 manage.py runserver
4. Install postgres following this post
5. Create database
$ createdb -U postgres testdb
6. Modify settings in django
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'testdb',
        'USER': 'postgres',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}
7. Migrate
$ python3 manage.py migrate
8. Create superuser
$ python3 manage.py createsuperuser

Postgres Cheat Sheet

1. Postgres version
$ psql --version

2. Connect to server locally using default postgres acct
$ psql -U postgres

3. Connect with a password
$ psql -U postgres -W

4. Common commands
\h - help
\q - quit
\l - list databases
\d - list tables
\d table - describe table

5. Create db
postgres=# CREATE DATABASE testdb;

6. Use the db
postgres=# \c testdb;
testdb=#

7. List of users
postgres=# \du

8. List postgres versions running
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.1 main    5433 down   postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
9.4 main    5432 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main    5434 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

9. Start/Stop/Restart/Status of postgresql
$ sudo systemctl start postgresql
$ sudo systemctl stop postgresql
$ sudo systemctl restart postgresql
$ sudo systemctl status postgresql

10. Backup/Restore
$ pg_dump -Fc -h hostname -p 5432 -d dbname -f /path/backupfilename
$ pg_restore -d dbname -v -1 /path/backupfilename

11. Cron daily backup
$ crontab -e
0 5 * * * export PGPASSWORD=MyPass && pg_dump -Fc -h hostname -p 5432 -d dbname -f /path/backupfilename_`date +20\%y\%m\%d` >> /path/pg_dump_dbname.log 2>&1

Install Postgres on Mac

Followed this post

1. Go to http://postgresapp.com/ then download and install
2. Add Postgres to PATH
$ vi ~/.bash_profile
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin
$ source ~/.bash_profile
3. Start Postgres by double clicking the app, then on the top icon, start "Initialize"
4. Try connecting using default postgres role
$ psql -U postgres
psql (9.6.3)
Type "help" for help.

postgres=#