Linux部署postgresql

# 添加postgres用户

[root@hadoop ~]# useradd postgres

[root@hadoop ~]# id postgres

uid=1001(postgres) gid=1001(postgres) 组=1001(postgres)

# 切换用户并创建目录

[root@hadoop postgres]# su - postgres

[postgres@hadoop ~]$ mkdir tmp sourcecode software shell log lib  data app

# 解压

[postgres@hadoop ~]$ tar -xvf software/postgresql-9.6.18-1-linux-x64-binaries.tar.gz -C ~/app/

[postgres@hadoop app]$ cd /home/postgres/app/pgsql/

# 创建数据目录

[postgres@hadoop ~]$ cd

[postgres@hadoop ~]$ cd data/

[postgres@hadoop data]$ mkdir pgdata

# 配置环境变量

[postgres@hadoop ~]$ vim .bashrc

export PG_HOME=/home/postgres/app/pgsql

export PATH=$PG_HOME/bin:$PATH

export PGDATA=/home/postgres/data/pgdata

[postgres@hadoop ~]$ source .bashrc

[postgres@hadoop ~]$ which psql 

~/app/pgsql/bin/psql

# 初始化数据库

[postgres@hadoop ~]$ initdb /home/postgres/data/pgdata

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".

The default database encoding has accordingly been set to "UTF8".

initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"

The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/data/pgdata ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default timezone ... PRC

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/postgres/data/pgdata -l logfile start

# 查看数据目录

[postgres@hadoop ~]$ cd /home/postgres/data/pgdata

[postgres@hadoop pgdata]$ ll

总用量 48

drwx------. 5 postgres postgres    38 6月  3 15:57 base

drwx------. 2 postgres postgres  4096 6月  3 15:57 global

drwx------. 2 postgres postgres    17 6月  3 15:57 pg_clog

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_commit_ts

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_dynshmem

-rw-------. 1 postgres postgres  4468 6月  3 15:57 pg_hba.conf

-rw-------. 1 postgres postgres  1636 6月  3 15:57 pg_ident.conf

drwx------. 4 postgres postgres    37 6月  3 15:57 pg_logical

drwx------. 4 postgres postgres    34 6月  3 15:57 pg_multixact

drwx------. 2 postgres postgres    17 6月  3 15:57 pg_notify

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_replslot

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_serial

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_snapshots

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_stat

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_stat_tmp

drwx------. 2 postgres postgres    17 6月  3 15:57 pg_subtrans

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_tblspc

drwx------. 2 postgres postgres    6 6月  3 15:57 pg_twophase

-rw-------. 1 postgres postgres    4 6月  3 15:57 PG_VERSION

drwx------. 3 postgres postgres    58 6月  3 15:57 pg_xlog

-rw-------. 1 postgres postgres    88 6月  3 15:57 postgresql.auto.conf

-rw-------. 1 postgres postgres 22505 6月  3 15:57 postgresql.conf

# 配置pg_hba.conf文件

[postgres@hadoop pgdata]$ cp pg_hba.conf pg_hba.conf.`date +%F`

[postgres@hadoop pgdata]$ vim pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                METHOD

# "local" is for Unix domain socket connections only

local  all            all                                    trust

# IPv4 local connections:

host    all            all            127.0.0.1/32            trust

# IPv6 local connections:

host    all            all            ::1/128                trust

host    all            all              0.0.0.0/0                md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local  replication    postgres                                trust

local  replication    all                                trust

#host    replication    postgres        127.0.0.1/32            trust

host    replication    all        127.0.0.1/32            trust

#host    replication    postgres        ::1/128                trust

# 配置postgresql.conf文件

[postgres@hadoop pgdata]$ cp postgresql.conf postgresql.conf.`date +%F`

[postgres@hadoop pgdata]$ vim postgresql.conf

listen_addresses = '*'

# 配置日志目录

[postgres@hadoop pgdata]$ mkdir ~/log/pglog

启动pg

[postgres@hadoop pgdata]$ pg_ctl start -l /home/postgres/log/pglog/pg_server.log

# 检验是否启动成功

[postgres@hadoop ~]$ cd

[postgres@hadoop ~]$ lsof -i:5432

COMMAND    PID    USER  FD  TYPE DEVICE SIZE/OFF NODE NAME

postgres 14122 postgres    3u  IPv4  35167      0t0  TCP *:postgres (LISTEN)

postgres 14122 postgres    4u  IPv6  35168      0t0  TCP *:postgres (LISTEN)

# 设置密码

[postgres@hadoop ~]$ psql

psql.bin (9.6.18)

Type "help" for help.

postgres=# \password

Enter new password:

Enter it again:

# 设置密码后需要重新登录

重启：

[postgres@hadoop pgdata]$ pg_ctl restart -l /home/postgres/log/pglog/pg_server.log