postgres - 安装和配置postgres, 以及基本的使用
访问量: 2532
参考：https://www.liquidweb.com/kb/what-is-the-default-password-for-postgresql/ 和 https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e
安装最新版10： 参考 https://www.postgresql.org/download/linux/ubuntu/ （可以安装最新版10 )
对于ubuntu 16.04:
1. sudo vim /etc/apt/sources.list.d/pgdg.list 内容为：
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
2. wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
3. sudo apt-get update
4. sudo apt-get install postgresql-10 postgresql-client-10 libpq-dev pgadmin4 postgresql-server-dev-10 postgresql-contrib
下面是安装9.x
安装： sudo apt-get install postgresql postgresql-contrib
登陆：
默认用户名是 postgres ，没有密码．
注意：它的验证方式是 ident, 也就是用 linux系统的用户名密码去验证． 这个跟mysql的不同． 安装好postgres数据库之后你的linux也多了一个用户名 postgres， 密码是空
所以需要对它进行修改．
进入到命令行
sudo -u postgres -i 进入到命令行模式． （注意，这里是进入到 linux 用户postgres的命令行模式）
创建用户（注意是在当前模式下，不是在psql 后： ) 注意，这个创建的是 linux user. 用户名是 myroot
createuser myroot
然后ctrl + d，回到 linux本来的ssh账户下。
然后
（shell) $ sudo -u postgres psql （这个就是登录 postgres 客户端）
(postgres 客户端命令）psql=# alter user <username> with encrypted password '<password>';
（貌似这样创建的用户在postgrest12下面是有权限问题的，
所以建议索性直接创建一个super user:
createuser --interactive -P
输入用户名，密码后，直接选择 superuser 即可
）
(对于postgres 13:
create user myroot with password '123456' superuser; )
该用户除了下面的授权，就可以使用了（见下面的grant all 语句）
使用 postgresAdmin来登录： （我觉得不好用。。。）
add new server -> 输入 用户名，密码，数据库名称。 保存即可。
基本用法 命令行：
$ sudo -u postgres -i
# psql
\l 查看数据库
\dt 查看表
\c 进入到数据库
创建数据库： $ create database my_cv;
\c <your database>
授权：grant all privileges on all tables in schema public to myroot; # schema 就是数据库, myroot就是用户名
查看角色：
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
myroot | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
myroot | Superuser, Create role, Create DB | {}
修改数据库：
alter database my_new_db owner to my_new_user;
ubuntu下的默认安装路径
/etc/postgresql/12/main
配置文件也在这里
默认的schema是public . （可以从postgreslql.conf 中看到）
查看文件存储位置：
/var/lib/postgresql/13/main
或者输入sql : show data_directory;
或者使用命令： pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file 13 main 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
日志
修改postgresql.conf, 确保以下的三个配置是合理的（ logging_collector = on ）
318 log_destination = 'syslog' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, 319 # depending on platform. csvlog 320 # requires logging_collector to be on. 321 322 # This is used when logging to stderr: 323 logging_collector = on # Enable capturing of stderr and csvlog 324 # into log files. Required to be on for 325 # csvlogs. 326 # (change requires restart) 327 328 # These are only used if logging_collector is on: 329 log_directory = '/var/log/postgresql' # directory where log files are written
命令行的方式：启动多个postgres实例
1. 安装 asdf，通过asdf安装多个postgres
2. 在不同目录下，就可以设置不通的默认postgres, 也可以通过asdf local postgres 9.5 来实现
pg_ctl start 可以启动“当前文件夹默认的”db
pg_ctl -o "-p 5433" start 可以把当前的 postgres版本以5433 来启动
所有的命令： pg_restore, pg_ctl, pg_dump 等，都可以通过输入 --version来查看对应的pg版本，特别好用。 例如9.5的pg下面的所有上述命令，都会返回 --version ... 9.5
修改配置文件的方式 启动多个postgre实例
修改 postgres~/.asdf/installs/postgres/9.5.25/data/postgresql.conf
port = 5433
port = 5432
修改这里就可以了。 只要port不重复就没问题
允许其他机器访问本机的服务
配置文件有2个：
~/.asdf/installs/postgres/11.12/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/8 trust 表示允许所有 127开头的ip地址访问
host all all 0.0.0.0/0 trust 表示允许所有的Ip地址访问
~/.asdf/installs/postgres/9.5.25/data/postgresql.conf
listen_addresses = '*' # 这个文件也要修改
SEQUENCE
有时候会需要用到sequence. 特别是对于数据库的restore之后，
creATE SEQUENCE login_histories_id_seq minvalue 11000;
ALTER TABLE login_histories ALTER id SET DEFAULT NEXTVAL('login_histories_id_seq');
drop SEQUENCE login_histories_id_seq cascade;
pg_restore pg_dump
$ pg_dump -U prd_user --host=remote_db_ip_or_domain --compress=5 --schema=public -Fc -o my_db_name > my_db_backup.dump