PostgreSQL

• https://www.postgresql.org/
• Oracle compatible open source database

Install on EC2

• from: https://github.com/snowplow/snowplow/wiki/Setting-up-PostgreSQL#ec2

• install postresql server

textCopy
sudo dnf install -y postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs
sudo postgresql-setup initdb

• configure access permission from outside
• need to open AWS Security Group port 5432

textCopy
[root@ip-172-31-23-71 data]# sudo su
cd /var/lib/pgsql/data

vim postgresql.conf

textCopy
#listen_addresses = 'localhost'          # what IP address(es) to listen on;
listen_addresses='*'

#port = 5432
port = 5432

• register account for development

textCopy
sudo service postgresql restart

vim /var/lib/pgsql/data/pg_hba.conf

textCopy
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             okuser          0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

• create develop db user

textCopy
sudo service postgresql restart

sudo su - postgres

textCopy
CREATE USER okuser NOSUPERUSER;
ALTER USER okuser WITH PASSWORD '$okuserPasswd';
CREATE DATABASE okdevdb WITH OWNER okuser;

\q

• Connection Info

textCopy
Host : 103.209.500.248
DB name : okdevdb
username : okuser
password : $okuserPasswd

Cases

• show databases;
  • \l
  • \l+
  • SELECT datname FROM pg_database;
• show tables;
  • \dt
• dump
  • pg_dump dbname > outfile
  • psql dbname < infile
  • pg_dump -h host1 dbname | psql -h host2 dbname
• ref
  • https://www.postgresql.org/docs/9.1/backup-dump.html

ref

• https://github.com/snowplow/snowplow/wiki/Setting-up-PostgreSQL#ec2