Back

postgres - 安装和配置postgres, 以及基本的使用

发布时间: 2018-03-09 10:49:00

参考: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不重复就没问题

允许其他机器访问本机的服务

参考:https://stackoverflow.com/questions/18580066/how-to-allow-remote-access-to-postgresql-database

配置文件有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

Back