PostgreSQL

# Commends

# access remotehost
psql -h ${REMOTE_HOST} -U ${USER} -d postgres

# list all databases
\l

# connect specific database
psql -h remote_host -U api -d mydatabase

Docker

Postgres - Official Image | Docker Hub

connect local(mac) DB without docker

$ psql postgres

connect to a remote database

$ psql -h <host> -p <port> -u <database>

# to AWS DB
# format 
$ psql -h <host> -p <port> -U <username> 

# e.g
$ psql -h <host> -p <port> -U postgres 

# $ psql -h <host> -p <port> -U <username> -W <password> <database>

Access postgreSQL of Docker container

if run docker on localhost and use port 5432

$ psql -h localhost -p 5432 -U postgres -W 

Connect From Your Local Machine to a PostgreSQL Database in DockerMedium

Access postgreSQL docker

$ docker exec -it ${CONTAINER_ID} /bin/bash
$ psql -U postgres

manual run & stop

How can I start PostgreSQL server on Mac OS X?Stack Overflow

The Homebrew package manager includes launchctl plists to start automatically. For more information run brew info postgres.

Start manually:

pg_ctl -D /usr/local/var/postgres start

Stop manually:

pg_ctl -D /usr/local/var/postgres stop

Start automatically:

"To have launchd start postgresql now and restart at login:"

brew services start postgresql

if you install manually follow this

$ cd /Users/x/PostgreSQL/pg11/bin/
$ ./pg_ctl -D /Users/x/PostgreSQL/data/pg11 stop 

$ ps -aux|grep postgres
/Users/x/PostgreSQL/pg11/bin/postgres -D /Users/x/PostgreSQL/data/pg11 -r /Users/x/PostgreSQL/data/logs/pg11/postgres.log

# pg_ctl -D /Users/x/PostgreSQL/data/pg11 stop 

PostgreSQL

Mac

download BigSQL (ui based installer)

https://www.bigsql.org/oscg_download.jsp?file=packages/PostgreSQL-9.6.2-2-osx64-bigsql.dmg&user=

run pgDevOps after install

how to run?

psql -U [userName]
# default$ psql -U postgres
# specif
$ psql -d mydb -U myuser

exit

$ \q

command line

\h

admin command line

\?

list roles

\du

create user

CREATE USER "local_test" WITH PASSWORD '123456'

change password

ALTER USER "user_name" WITH PASSWORD 'new_password';

create database

create database ror5_tutorial with owner = 'ror5';

Alter database

ALTER DATABASE name OWNER TO new_owner

Commands

List db

PSQL

\list

select query

SELECT datname FROM pg_database;

psql -U postgres \connect ror5_tutorial

list table

\dt

Error

Socket File “/var/pgsql_socket/.s.PGSQL.5432” Missing InMountain Lion (OS X Server)

http://stackoverflow.com/questions/13868730/socket-file-var-pgsql-socket-s-pgsql-5432-missing-in-mountain-lion-os-x-ser

=> solution

setting in .bash_profile

export PGHOST=localhost

issue

Can't run psql command, keep getting the same error

dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
Referenced from: /usr/local/bin/psql
Reason: image not found
[1]    69711 trace trap  psql

Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylibStack Overflow

int type change to UUID

up

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
ALTER TABLE target_table ADD COLUMN new_id UUID NULL;
UPDATE messages SET new_id = CAST(LPAD(TO_HEX(id), 32, '0') AS UUID);
ALTER TABLE target_table DROP COLUMN id;
ALTER TABLE target_table RENAME COLUMN new_id TO id;
ALTER TABLE target_table ALTER COLUMN id SET NOT NULL;

down

       CREATE SEQUENCE target_table_id_seq;
       ALTER TABLE target_table
          DROP COLUMN id;
       ALTER TABLE target_table
          ADD COLUMN id INTEGER NOT NULL DEFAULT nextval('target_table_id_seq');
       ALTER SEQUENCE target_table_id_seq OWNED BY target_table.id;

References

• How to persist data in a dockerized postgres database using volumes