linux postgresql配置,Linux上PostgreSQL的装配与配置

以RHEL 7上安装PostgreSQL 9.6为例：

安装

Install the repository RPM:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

Install the client packages:

yum install postgresql96

Install the server packages:

yum install postgresql96-server

Initialize the database and enable automatic start:

/usr/pgsql-9.6/bin/postgresql96-setup initdb

systemctl enable postgresql-9.6

systemctl start postgresql-9.6

配置

编辑/var/lib/pgsql/9.6/data/postgresql.conf，修改listen_addresses，监听所有地址：

listen_addresses = '*'

编辑/var/lib/pgsql/9.6/data/pg_hba.conf，修改验证方式：

# "local" is for Unix domain socket connections only

local   all             all                                     trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            trust

host    all             all             0.0.0.0/0               password

说明：

trust  本地可以使用psql -U postgres直接登录服务器

peer  本地可以使用psql -h 127.0.0.1 -d postgres -U postgres直接登录服务器

重启PostgreSQL

systemctl restart postgresql-9.6

PSQL

连接PostgreSQL

psql -U postgres

修改配置文件后，可执行以下命令刷新配置：

select pg_reload_conf();

更改密码：

ALTER USER postgres WITH PASSWORD 'postgres';

查看用户：

select * from pg_shadow;

查看data文件夹所在目录：

show data_directory;

创建用户

CREATE USER test WITH PASSWORD 'test';

ALTER USER test WITH SUPERUSER;

创建SCHEMA

CREATE SCHEMA test;

ALTER SCHEMA test OWNER TO test;

查看SCHEMA

\dn

设置Search Path

SET search_path TO test;

执行sql脚本

\i test.sql

Sequence

查询sequence(currval(), nextval())

select nextval('test_sequence');

更新sequence

alter sequence test_sequence restart with 42;

退出

\q

帮助

help

\?

\h

备份与恢复

pg_dump -h host1 dbname > outfile

psql -U postgres dbname < infile

也可直接备份data目录

tar -cf backup.tar /usr/local/pgsql/data

Linux downloads (Red Hat family)