Ubuntu下的postgres

1. ubuntu切换国内源, 更新apt

阿里源链接:

deb http://mirrors.aliyun.com/ubuntu/ bionic main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-security main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-updates main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-proposed main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-backports main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-security main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-updates main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-proposed main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-backports main restricted universe multiverse

备份原文件:

sudo cp /etc/apt/sources.list /etc/apt/sources.list.bak

编辑apt的sourcelist, 把阿里源的配置粘贴进去:

vim sources.list

安装postgres

sudo apt-get install postgresql
sudo apt-get install postgresql-contrib
sudo apt-get install postgresql-client

启动postgres server

sudo service postgresql start

修改postgres数据库用户密码

psql -h 127.0.0.1 -U postgres -p 5454
ALTER USER postgres WITH PASSWORD ‘123456’;
ALTER ROLE postgres WITH PASSWORD ‘123456’;

退出

\q

修改ubuntu下postgres用户密码

su root
sudo passwd -d postgres 清除现有密码
sudo -u postgres passwd 设置密码

远程登录配置

sudo vim /etc/postgresql/9.6/main/postgresql.conf

1. #listen_addresses=‘localhost’ 改为 listen_addresses=’*’
2. 启用密码验证，将 #password_encryption = on 前面的 # 号去掉

vim /etc/postgresql/9.6/main/pg_hba.conf
1：在文档末尾加上以下内容
host all all 0.0.0.0 0.0.0.0 md5

重启服务

sudo service postgresql restart

备份库

pg_dump -h 192.168.2.235 -U postgres kuming > d:\路径
or
pg_dump dn_name | gzip > /路径/bak_name.gz

恢复库

psql -h 127.0.0.1 -p 5433 -U postgres -d kuming < d:\路径
or
gunzip -c bak_name.gz | psql db_name