《课程笔记2 | PostgreSQL深入浅出》
《课程笔记1 | PostgreSQL深入浅出》-linux安装pg 12(source)
1.安装
--创建目录
[root@single data]# mkdir -p /usr/local/pgsql
[root@single data]# mkdir -p /usr/local/pgsql/pgdata
[root@single data]# mkdir -p /usr/local/pgsql/pglog
[root@single data]# mkdir -p /usr/local/pgsql/pgarch
[root@single data]#
[root@single data]# cd /usr/local/pgsql && pwd
/usr/local/pgsql
[root@single pgsql]# ll /usr/local/pgsql
total 16
drwxr-xr-x 2 root root 4096 Aug 29 18:12 bin
drwxr-xr-x 6 root root 4096 Aug 29 18:11 include
drwxr-xr-x 4 root root 4096 Aug 29 18:12 lib
drwxr-xr-x 2 root root 6 Aug 29 18:18 pgarch
drwxr-xr-x 2 root root 6 Aug 29 18:18 pgdata
drwxr-xr-x 2 root root 6 Aug 29 18:18 pglog
drwxr-xr-x 8 root root 4096 Aug 29 18:11 share
--创建用户
[root@single pgsql]# useradd -u 2000 postgres
[root@single pgsql]# echo postgres | passwd --stdin postgres
Changing password for user postgres.
passwd: all authentication tokens updated successfully.
[root@single pgsql]#
[root@single pgsql]#
[root@single pgsql]# id postgres && ll /home/
uid=2000(postgres) gid=2000(postgres) groups=2000(postgres)
total 4
drwx------ 4 ftpuser ftpuser 240 Aug 26 23:38 ftpuser
drwx------. 15 oracle oinstall 4096 Mar 22 19:39 liang
drwx------ 5 oracle oinstall 267 Aug 24 00:40 oracle
drwx------ 3 postgres postgres 92 Aug 29 18:20 postgres
--解压安装包(在/root下)
[root@single pgsql]# cd ~
[root@single ~]# tar zxf postgresql-12.12.tar.gz -C /usr/local/pgsql
[root@single ~]# ll /usr/local/pgsql
total 16
drwxr-xr-x 2 root root 4096 Aug 29 18:12 bin
drwxr-xr-x 6 root root 4096 Aug 29 18:11 include
drwxr-xr-x 4 root root 4096 Aug 29 18:12 lib
drwxr-xr-x 2 root root 6 Aug 29 18:18 pgarch
drwxr-xr-x 2 root root 6 Aug 29 18:18 pgdata
drwxr-xr-x 2 root root 6 Aug 29 18:18 pglog
drwxrwxrwx 6 1107 1107 323 Aug 9 05:07 postgresql-12.12
drwxr-xr-x 8 root root 4096 Aug 29 18:11 share
[root@single ~]#
yum install -y libicu-devel zlib-devel readline readline-devel perl-ExtUtils-Embed perl-ExtUtils-MakeMaker pam-devel openssl-devel openldap-devel libxml2-devel libxslt-devel systemd-devel tcl-devel gcc net-tools python python-devel
cd /usr/local/pgsql/postgresql-12.12/ && mv * ..
cd .. && rm -rf postgresql-12.12 && ls
export PREFIX=/usr/local/pgsql
export PGPORT=5432
./configure
–prefix={PREFIX} \ --exec-prefix={PREFIX}
–bindir={PREFIX}/bin \ --sysconfdir={PREFIX}/etc
–libdir={PREFIX}/lib \ --includedir={PREFIX}/include
–datarootdir={PREFIX}/share \ --datadir={PREFIX}/share
–localedir={PREFIX}/share \ --mandir={PREFIX}/share/man
–docdir={PREFIX}/share/doc \ --htmldir={PREFIX}/share/html
\–enable-nls=“zh_CN en_US”
\–with-perl
\–with-python
\–with-tcl
\–with-icu
\–with-openssl
\–with-ldap
\–with-pam
\–with-systemd
\–with-libxml
\–with-libxslt
\–with-readline
\–with-zlib
\–with-pgport=${PGPORT}
\–with-segsize=1
\–with-blocksize=8
\–with-wal-blocksize=8
--编译安装
make world
make install-world
--安装插件
cd /usr/local/pgsql/contrib
make
make install
--配置环境变量
su - postgres
cat >>/home/postgres/.bash_profile<<EOF
export PGHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}
export PGDATA=${PGHOME}/pgdata
export PATH=${PGHOME}/bin:${PATH}
EOF
source ~//.bash_profile && . ~/.bash_profile
chown -R postgres:postgres /usr/local/pgsql
2.初始化
[root@single contrib]# ll -d /usr/local/pgsql/pgdata && ll -l /usr/local/pgsql/pgdata
drwxr-xr-x 2 postgres postgres 6 Aug 29 18:18 /usr/local/pgsql/pgdata
total 0
[root@single contrib]#
su – postgres
initdb -D $PGDATA -W -U postgres
[postgres@single ~]$ initdb -D $PGDATA -W -U postgres
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.
Enter new superuser password:
Enter it again:
fixing permissions on existing directory /usr/local/pgsql/pgdata ... initdb: error: could not change permissions of directory "/usr/local/pgsql/pgdata": Operation not permitted
[postgres@single ~]$ initdb -D $PGDATA -W -U postgres
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.
Enter new superuser password:
Enter it again:
fixing permissions on existing directory /usr/local/pgsql/pgdata ... 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:
pg_ctl -D /usr/local/pgsql/pgdata -l logfile start
[postgres@single ~]$
--修改配置文件postgresql.conf
vi /usr/local/pgsql/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432
--修改配置文件pg_hba.conf
vi /usr/local/pgsql/pgdata/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
--启动数据库
pg_ctl -D /usr/local/pgsql/pgdata -l logfile start
[postgres@single pgdata]$ pg_ctl -D /usr/local/pgsql/pgdata -l logfile start
waiting for server to start.... done
server started
[postgres@single pgdata]$
[postgres@single pgdata]$ ps -ef | grep postgres
root 44766 2291 0 18:30 pts/0 00:00:00 su - postgres
postgres 44768 44766 0 18:30 pts/0 00:00:00 -bash
postgres 51886 1 0 18:39 ? 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/pgdata
postgres 51888 51886 0 18:39 ? 00:00:00 postgres: checkpointer
postgres 51889 51886 0 18:39 ? 00:00:00 postgres: background writer
postgres 51890 51886 0 18:39 ? 00:00:00 postgres: walwriter
postgres 51891 51886 0 18:39 ? 00:00:00 postgres: autovacuum launcher
postgres 51892 51886 0 18:39 ? 00:00:00 postgres: stats collector
postgres 51893 51886 0 18:39 ? 00:00:00 postgres: logical replication launcher
postgres 51918 44768 0 18:39 pts/0 00:00:00 ps -ef
postgres 51919 44768 0 18:39 pts/0 00:00:00 grep --color=auto postgres
[postgres@single pgdata]$
[root@single pgsql]# netstat -tpnl |grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 51886/postgres
tcp6 0 0 :::5432 :::* LISTEN 51886/postgres
[root@single pgsql]#
[root@single pgsql]#
3.查看
[postgres@single pgdata]$ psql -d postgres
psql (12.12)
Type "help" for help.
postgres=# \l --列出所有数据库
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+-------+----------+---------+-------+-------------------
(0 rows)
postgres=#
postgres=# \du --显示所有用户
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
postgres=# create database test1;
CREATE DATABASE
postgres=# \c test1;
You are now connected to database "test1" as user "postgres".
test1=# \conninfo
You are connected to database "test1" as user "postgres" via socket in "/tmp" at port "5432".
test1=#
免责声明:
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源,请您支持作者,购买正版内容。
6、资源失效,请下方留言,欢迎分享资源链接
文章评论