1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
| ## Install postgres
vim /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main
# or
# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update
apt-get upgrade
apt-get install postgresql-9.4
apt-get install libpq-dev
## Change postgres password
su - postgres
psql
\password postgres
## Create new user, database and grant premission in postgres
CREATE USER <USER> WITH PASSWORD '<PASSWORD>';
CREATE DATABASE <USER_DATABASE> OWNER <USER>
GRANT ALL PRIVILEGES ON DATABASE <USER_DATABASE> to <USER>;
\q
## Change postgres user password
su -
# output: "passwd: password expiry information changed."
passwd -d postgres
postgres -c passwd
## Update configuation
vim /etc/postgresql/9.4/main/postgresql.conf
listen_addresses = 'localhost'
password_encryption = on
vim /etc/postgresql/9.4/main/pg_hba.conf
***>> change `peer` to `md5`***
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all **peer -> md5**
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
## Restart postgres
/etc/init.d/postgresql restart
## Try user login
psql -U<USER> <USER_DATABASE>
## Install pgloader (Local Machine)
brew install postgres
brew install pgloader
pgloader sqlite2pg.load
## Export database (Local Machine)
pg_dump --host=localhost --port=5432 --username="<USER>" --password --format=c --blobs --verbose --file="/Users/<USER>/Desktop/database.backup" "<USER_DATABASE>"
## Upload to remote machine (Local Machine)
scp -P 1888 /Users/<USER>/Desktop/database.backup <USER>@<HOST>:~/path/to/project/
## Import database (Remote Machine)
pg_restore --host=localhost --port=5432 --username="<USER>" --dbname "<USER_DATABASE>" --password --verbose "/path/to/project/database.backup"
## Problem
#### Flask SQLAlchemy raise exception "SSL SYSCALL error: EOF detected"
Edit `uwsgi.ini`
lazy-apps = true
Or add code to before request
@app.before_request
def before_request():
db.engine.dispose()
Reference
- https://github.com/mitsuhiko/flask-sqlalchemy/issues/268
- http://bocribbz.com/post/64718784142/interfaceerror-interfaceerror-connection
- http://stackoverflow.com/questions/15410248/celery-sqlalchemy-databaseerror-databaseerror-ssl-error-decryption-faile
- http://stackoverflow.com/questions/22752521/uwsgi-flask-sqlalchemy-and-postgres-ssl-error-decryption-failed-or-bad-reco
## Postgres Reference
- https://wiki.postgresql.org/wiki/Apt
- http://book.tp.edu.tw/ecampus/INSTALL/2.1.htm
- http://netkiller.github.io/database/postgresql/pgsql.conf.html
|
