Postgresql

References

• Installing PostgreSQL 9.0 on Mac OS X 10.6.8 via MacPorts

user chris History

  502  mvim .bash_profile
  503  which psql
  504  sudo rm `which psql`
  505  ls /opt/local/bin/ | less
  506  port contents postgresql90
  507  which psql
  508  port installed | grep postgresql
  509  port contents postgresql_select
  510  cat /opt/local/etc/select/postgresql/base
  511  cat /opt/local/etc/select/postgresql/none
  512  sudo port install postgresql90-server
  513  which postgres
  514  man mkdir
  515  sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
  516  sudo chown -R postgres:postgres /opt/local/var/db/postgresql90
  517  sudo mkdir -p /opt/local/var/log/postgresql90
  518  sudo chown -R postgres:postgres /opt/local/var/log/postgresql90
  519  sudo -u postgres /opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb
  520  which postgres
  521  sudo dscl . -create /Users/postgres UserShell /bin/bash
  522  man dscl
  523  dscl . -read /Users/postgres
  524  dscl . -read /Groups/postgres
  525  ls
  526  mvim .bashrc 
  527  find /opt/local/ -name "pg_hba.conf"
  528  sudo find /opt/local/ -name "pg_hba.conf"
  529  sudo mvim /opt/local//var/db/postgresql90/defaultdb/pg_hba.conf
  530  sudo su - postgres
  531  sudo su - postgres
  532  /opt/local/lib/postgresql90/bin/createdb
  533  sudo su - postgres
  534  /opt/local/lib/postgresql90/bin/createdb
  535  echo $PATH
  536  source .bashrc
  537  echo $PATH
  538  hi

user postgres History

$ sudo su - postgres
Password:
NIA-LG-01778617:~ postgres$ hi
-bash: hi: command not found
NIA-LG-01778617:~ postgres$ history
    1  pwd
    2  vim ./pg_start
    3  vim ./pg_stop
    4  chmod +x ./pg_stop 
    5  chmod +x ./pg_start 
    6  ./pg_start 
    7  cat ../../log/postgresql90/postgres.log 
    8  /opt/local/lib/postgresql90/bin/createuser colettace
    9  exit
   10  /opt/local/lib/postgresql90/bin/dropuser colettace
   11  /opt/local/lib/postgresql90/bin/createuser chris
   12  hi
   13  history
NIA-LG-01778617:~ postgres$ pwd
/opt/local/var/db/postgresql90

Directions after Install

Postgres9.3

To create a database instance, after install do
 sudo mkdir -p /opt/local/var/db/postgresql93/defaultdb
 sudo chown postgres:postgres /opt/local/var/db/postgresql93/defaultdb
 sudo su postgres -c '/opt/local/lib/postgresql93/bin/initdb -D /opt/local/var/db/postgresql93/defaultdb' 

Postgres10.4

• Creating user, database and adding access on PostgreSQL

Caviats from Homebrew

To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start

Create user

$createuser --interactive chris_psql_user
Shall the new role be a superuser? (y/n) y
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

Create DB

createdb lgfreezer

Change password

• psql -d lgfreezer -h localhost -U chris_psql_user
• lgfreezer=> alter user <username> with encrypted password '<password>';

Using in Python

import psycopg2

import sqlalchemy 

import pandas as pd

conn_string = "host='localhost' dbname='lgfreezer' user='chris_psql_user2' password='sadkjhasfkljhdas'"

conn = psycopg2.connect(conn_string)

query.close()

query = open('freezer_locations.sql')

locations = pd.read_sql_query( query.read(), conn )

Turn server off

pg_ctl stop -D tempdb_dir/