[go: up one dir, main page]

0% found this document useful (0 votes)
24 views4 pages

postgresql安装

postgresql安装

Uploaded by

huangzm.routon
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
24 views4 pages

postgresql安装

postgresql安装

Uploaded by

huangzm.routon
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

1.

前提条件
make

3.80版本以上

make --version
GNU Make 3.82

readline

yum install readline*


rpm -qa|grep readline
readline-static-6.2-11.el7.x86_64
readline-6.2-11.el7.x86_64
readline-devel-6.2-11.el7.x86_64

zlib

yum install zlib*


rpm -qa|grep zlib
zlib-static-1.2.7-19.el7_9.x86_64
zlib-1.2.7-19.el7_9.x86_64
zlib-devel-1.2.7-19.el7_9.x86_64

2.安装部署
创建用户

groupadd postgres
useradd -g postgres postgres
passwd postgres

创建目录

df -Th
mkdir -p /data/postgres/13.3
chown -R postgres:postgres /data

安装
以postgres账号登录
tar zxvf postgresql-13.3.tar.gz
cd postgresql-13.3
./configure --help
./configure --prefix=/data/postgres/13.3

make world 耗时比较长


make install-world

初始化数据库

/data/postgres/13.3/bin/initdb -D /data/postgres/13.3/data

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 "en_US.UTF-8".


The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /data/postgres/13.3/data ... ok


creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

/data/postgres/13.3/bin/pg_ctl -D /data/postgres/13.3/data -l logfile start

[postgres@db01 data]$ ll
total 52
drwx------ 5 postgres postgres 41 May 22 17:51 base
drwx------ 2 postgres postgres 4096 May 22 17:51 global
drwx------ 2 postgres postgres 6 May 22 17:50 pg_commit_ts
drwx------ 2 postgres postgres 6 May 22 17:50 pg_dynshmem
-rw------- 1 postgres postgres 4760 May 22 17:50 pg_hba.conf
-rw------- 1 postgres postgres 1636 May 22 17:50 pg_ident.conf
drwx------ 4 postgres postgres 68 May 22 17:51 pg_logical
drwx------ 4 postgres postgres 36 May 22 17:50 pg_multixact
drwx------ 2 postgres postgres 6 May 22 17:50 pg_notify
drwx------ 2 postgres postgres 6 May 22 17:50 pg_replslot
drwx------ 2 postgres postgres 6 May 22 17:50 pg_serial
drwx------ 2 postgres postgres 6 May 22 17:50 pg_snapshots
drwx------ 2 postgres postgres 6 May 22 17:50 pg_stat
drwx------ 2 postgres postgres 6 May 22 17:50 pg_stat_tmp
drwx------ 2 postgres postgres 18 May 22 17:50 pg_subtrans
drwx------ 2 postgres postgres 6 May 22 17:50 pg_tblspc
drwx------ 2 postgres postgres 6 May 22 17:50 pg_twophase
-rw------- 1 postgres postgres 3 May 22 17:50 PG_VERSION
drwx------ 3 postgres postgres 60 May 22 17:50 pg_wal
drwx------ 2 postgres postgres 18 May 22 17:50 pg_xact
-rw------- 1 postgres postgres 88 May 22 17:50 postgresql.auto.conf
-rw------- 1 postgres postgres 28023 May 22 17:50 postgresql.conf

设置环境变量

vi .bash_profile
export PGDATA=/data/postgres/13.3/data
export PATH=/data/postgres/13.3/bin:$PATH

psql
\q

pg_hba.conf

host all all 0.0.0.0/0 md5

postgres.conf

listen_addresses = '*'

重启

pg_ctl restart -m fast

关闭

pg_ctl stop -m fast


-m fast:表示以fast模式快速干净的关闭数据库,关闭过程中回回滚未提交的事务,下次启动无需进行
实例恢复。
MODE can be "smart", "fast", or "immediate"
smart quit after all clients have disconnected
fast quit directly, with proper shutdown (default)
immediate quit without complete shutdown; will lead to recovery on restart

启动

如果设置了PGDATA环境变量:
pg_ctl start -l logfile

查看状态
pg_ctl status
pg_ctl: server is running (PID: 2647)
/data/postgres/13.3/bin/postgres

pstree -p 2647
postgres(2647)─┬─postgres(2649)
├─postgres(2650)
├─postgres(2651)
├─postgres(2652)
├─postgres(2653)
└─postgres(2654)

3.psql使用
psql连接数据库

psql -h localhost -p 5432 -d postgres -U postgres

\c 列出当前用户
\d 列出对象
create table t1(name text);
\d t1 列出表结构
\h create table 查看帮助
select version();
\l 列出数据库
\l+ 包含表空间、大小

select pg_postmaster_start_time();数据库启动时间

\du 查看用户
\dt+ t1 查看t1表大小

\di 查看索引
create index idx_name on t1(name);
\di+ idx_name 查看索引大小

创建用户:
\h create user
\h create role
create user fx superuser password '123456';

创建数据库:
create database testdb owner fx;
\c testdb fx 使用fx用户连接testdb

查看表空间:
\db

查看视图:
\dv

You might also like