Windows 环境下:
1. Initab -D "D:\Soft\PosgreSQLTest"
Note: 首先创建一个空文件夹 PostgreSQLTest
D:\Soft\PosgreSQLTest>initdb --help
initdb initializes a PostgreSQL database cluster.
Usage:
initdb [OPTION]... [DATADIR]
Options:
-A, --auth=METHOD default authentication method for local connections
--auth-host=METHOD default authentication method for local TCP/IP connections
--auth-local=METHOD default authentication method for local-socket connections
[-D, --pgdata=]DATADIR location for this database cluster
-E, --encoding=ENCODING set default encoding for new databases
-g, --allow-group-access allow group read/execute on data directory
--icu-locale=LOCALE set ICU locale ID for new databases
-k, --data-checksums use data page checksums
--locale=LOCALE set default locale for new databases
--lc-collate=, --lc-ctype=, --lc-messages=LOCALE
--lc-monetary=, --lc-numeric=, --lc-time=LOCALE
set default locale in the respective category for
new databases (default taken from environment)
--no-locale equivalent to --locale=C
--locale-provider={libc|icu}
set default locale provider for new databases
--pwfile=FILE read password for the new superuser from file
-T, --text-search-config=CFG
default text search configuration
-U, --username=NAME database superuser name
-W, --pwprompt prompt for a password for the new superuser
-X, --waldir=WALDIR location for the write-ahead log directory
--wal-segsize=SIZE size of WAL segments, in megabytes
Less commonly used options:
-d, --debug generate lots of debugging output
--discard-caches set debug_discard_caches=1
-L DIRECTORY where to find the input files
-n, --no-clean do not clean up after errors
-N, --no-sync do not wait for changes to be written safely to disk
--no-instructions do not print instructions for next steps
-s, --show show internal settings
-S, --sync-only only sync database files to disk, then exit
Other options:
-V, --version output version information, then exit
-?, --help show this help, then exit
If the data directory is not specified, the environment variable PGDATA
is used.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
D:\Soft\PosgreSQLTest>initdb -D "D:\Soft\PosgreSQLTest"
The files belonging to this database system will be owned by user "Maxwell Pan".
This user must also own the server process.
The database cluster will be initialized with locale "English_United States.1252".
The default database encoding has accordingly been set to "WIN1252".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory D:/Soft/PosgreSQLTest ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
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
initdb: hint: 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 ^"D^:^\Soft^\PosgreSQLTest^" -l logfile start
D:\Soft\PosgreSQLTest>
2. 查看数据库状态:
C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>pg_ctl status
pg_ctl: no server running
C:\Users\Maxwell Pan>
3.启动数据库
C:\Users\Maxwell Pan>pg_ctl start
waiting for server to start....2023-04-23 19:25:01.721 CST [4192] LOG: redirecting log output to logging collector process
2023-04-23 19:25:01.721 CST [4192] HINT: Future log output will appear in directory "log".
. done
server started
C:\Users\Maxwell Pan>
4. 登录postgres数据库
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# show data_directory;
data_directory
---------------------------
D:/Soft/PostgreSQL15/data
(1 row)
postgres=# show port;
port
------
5432
(1 row)
postgres=# exit
C:\Users\Maxwell Pan>
5.停止数据库
C:\Users\Maxwell Pan>pg_ctl stop
waiting for server to shut down.... done
server stopped
C:\Users\Maxwell Pan>psql -U postgres
psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D/10061)
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused (0x0000274D/10061)
Is the server running on that host and accepting TCP/IP connections?
C:\Users\Maxwell Pan>
6. 改变数据库相关参数(重启数据库后生效)
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=#
postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=# alter system set work_mem='5MB';
ALTER SYSTEM
postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=# \q
C:\Users\Maxwell Pan>pg_ctl stop
waiting for server to shut down.... done
server stopped
C:\Users\Maxwell Pan>pg_ctl start
waiting for server to start....2023-04-23 19:31:54.671 CST [9796] LOG: redirecting log output to logging collector process
2023-04-23 19:31:54.671 CST [9796] HINT: Future log output will appear in directory "log".
done
server started
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# show work_mem;
work_mem
----------
5MB
(1 row)
postgres=# exit
7.查看 pg_controldata
C:\Users\Maxwell Pan>pg_controldata
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7224838632330247656
Database cluster state: in production
pg_control last modified: 4/23/2023 7:31:54 PM
Latest checkpoint location: 0/1560798
Latest checkpoint's REDO location: 0/1560798
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:737
Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 717
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: 4/23/2023 7:31:31 PM
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 1717297916a132647a9ec3bb606535c1e0bbbc0f9b86654cdafdc68791d65b89
C:\Users\Maxwell Pan>
8.创建新数据库
C:\Users\Maxwell Pan>pg_ctl status
pg_ctl: server is running (PID: 9796)
D:/Soft/PostgreSQL15/bin/postgres.exe
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
postgres=#
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-----
postgres | 5
template1 | 1
template0 | 4
(3 rows)
postgres=# create database employees owner postgres;
CREATE DATABASE
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 5
employees | 24576
template1 | 1
template0 | 4
(4 rows)
postgres=# \q
C:\Users\Maxwell Pan>
9. 删除数据库
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-------
postgres | 5
employees | 24576
template1 | 1
template0 | 4
(4 rows)
postgres=# drop database employees;
DROP DATABASE
postgres=# select datname,oid from pg_database;
datname | oid
-----------+-----
postgres | 5
template1 | 1
template0 | 4
(3 rows)
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
postgres=# \q
C:\Users\Maxwell Pan>
10. 查看最大关联数
C:\Users\Maxwell Pan>pg_ctl status
pg_ctl: server is running (PID: 9796)
D:/Soft/PostgreSQL15/bin/postgres.exe
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=#
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
postgres=#
postgres=# select name,source,boot_val,sourcefile,pending_restart from pg_settings where name='max_connections';
name | source | boot_val | sourcefile | pending_restart
-----------------+--------------------+----------+-------------------------------------------+-----------------
max_connections | configuration file | 100 | D:/Soft/PostgreSQL15/data/postgresql.conf | f
(1 row)
postgres=# \d pg_settings
View "pg_catalog.pg_settings"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
name | text | | |
setting | text | | |
unit | text | | |
category | text | | |
short_desc | text | | |
extra_desc | text | | |
context | text | | |
vartype | text | | |
source | text | | |
min_val | text | | |
max_val | text | | |
enumvals | text[] | | |
boot_val | text | | |
reset_val | text | | |
sourcefile | text | | |
sourceline | integer | | |
pending_restart | boolean | | |
postgres=# exit
11.修改相关参数,重载后生效。
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# select * from pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# select * from pg_file_settings;
sourcefile | sourceline | seqno | name | setting | applied | error
------------------------------------------------+------------+-------+----------------------------+----------------------------+---------+-------
D:/Soft/PostgreSQL15/data/postgresql.conf | 60 | 1 | listen_addresses | * | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 64 | 2 | port | 5432 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 65 | 3 | max_connections | 100 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 127 | 4 | shared_buffers | 128MB | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 150 | 5 | dynamic_shared_memory_type | windows | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 241 | 6 | max_wal_size | 1GB | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 242 | 7 | min_wal_size | 80MB | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 444 | 8 | log_destination | stderr | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 451 | 9 | logging_collector | on | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 461 | 10 | log_file_mode | 0640 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 597 | 11 | log_timezone | Asia/Shanghai | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 711 | 12 | datestyle | iso, mdy | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 713 | 13 | timezone | Asia/Shanghai | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 727 | 14 | lc_messages | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 729 | 15 | lc_monetary | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 730 | 16 | lc_numeric | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 731 | 17 | lc_time | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 734 | 18 | default_text_search_config | pg_catalog.english | t |
D:/Soft/PostgreSQL15/data/postgresql.auto.conf | 3 | 19 | work_mem | 5MB | t |
(19 rows)
postgres=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
postgres=# show work_mem;
work_mem
----------
5MB
(1 row)
postgres=# alter system set work_mem='10MB';
ALTER SYSTEM
postgres=# select * from pg_file_settings;
sourcefile | sourceline | seqno | name | setting | applied | error
------------------------------------------------+------------+-------+----------------------------+----------------------------+---------+-------
D:/Soft/PostgreSQL15/data/postgresql.conf | 60 | 1 | listen_addresses | * | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 64 | 2 | port | 5432 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 65 | 3 | max_connections | 100 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 127 | 4 | shared_buffers | 128MB | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 150 | 5 | dynamic_shared_memory_type | windows | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 241 | 6 | max_wal_size | 1GB | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 242 | 7 | min_wal_size | 80MB | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 444 | 8 | log_destination | stderr | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 451 | 9 | logging_collector | on | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 461 | 10 | log_file_mode | 0640 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 597 | 11 | log_timezone | Asia/Shanghai | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 711 | 12 | datestyle | iso, mdy | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 713 | 13 | timezone | Asia/Shanghai | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 727 | 14 | lc_messages | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 729 | 15 | lc_monetary | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 730 | 16 | lc_numeric | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 731 | 17 | lc_time | English_United States.1252 | t |
D:/Soft/PostgreSQL15/data/postgresql.conf | 734 | 18 | default_text_search_config | pg_catalog.english | t |
D:/Soft/PostgreSQL15/data/postgresql.auto.conf | 3 | 19 | work_mem | 10MB | t |
(19 rows)
postgres=# select * from pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# \q
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# show work_mem;
work_mem
----------
10MB
(1 row)
postgres=# alter system reset all;
ALTER SYSTEM
postgres=# select * from pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
postgres=# create database employees owner postgres;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
employees | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# create database temp owner postgres;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+----------------------------+----------------------------+------------+-----------------+-----------------------
employees | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
temp | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(5 rows)
postgres=#
Linux环境下
1. initially
initdb -D /var/lib/pgsql/13/data
2. check out
[postgres@postgres data]$ ls -ltr
total 64
-rw------- 1 postgres postgres 3 Nov 21 20:41 PG_VERSION
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_twophase
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_tblspc
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_snapshots
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_serial
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_replslot
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_notify
drwx------ 4 postgres postgres 36 Nov 21 20:41 pg_multixact
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_dynshmem
drwx------ 2 postgres postgres 6 Nov 21 20:41 pg_commit_ts
-rw------- 1 postgres postgres 88 Nov 21 20:41 postgresql.auto.conf
-rw------- 1 postgres postgres 1636 Nov 21 20:41 pg_ident.conf
drwx------ 2 postgres postgres 18 Nov 21 20:41 pg_xact
drwx------ 3 postgres postgres 60 Nov 21 20:41 pg_wal
drwx------ 2 postgres postgres 18 Nov 21 20:41 pg_subtrans
drwx------ 5 postgres postgres 41 Nov 21 20:41 base
drwx------ 2 postgres postgres 32 Nov 21 20:42 log
-rw------- 1 postgres postgres 28093 Nov 21 20:50 postgresql.conf
-rw------- 1 postgres postgres 4580 Nov 21 21:01 pg_hba.conf
-rw------- 1 postgres postgres 30 Nov 21 21:29 current_logfiles
-rw------- 1 postgres postgres 58 Nov 21 21:29 postmaster.opts
-rw------- 1 postgres postgres 95 Nov 21 21:29 postmaster.pid
drwx------ 2 postgres postgres 6 Nov 21 21:29 pg_stat
drwx------ 2 postgres postgres 4096 Nov 21 21:30 global
drwx------ 4 postgres postgres 68 Nov 21 21:34 pg_logical
drwx------ 2 postgres postgres 63 Apr 23 19:35 pg_stat_tmp
[postgres@postgres data]$
3.启动数据库
[postgres@postgres data]$ pg_ctl start
waiting for server to start....2023-04-23 20:00:01.817 HKT [40452] LOG: redirecting log output to logging collector process
2023-04-23 20:00:01.817 HKT [40452] HINT: Future log output will appear in directory "log".
done
server started
[postgres@postgres data]$
4.查看数据库状态。
[postgres@postgres data]$ pg_ctl status
pg_ctl: server is running (PID: 1382)
/usr/pgsql-13/bin/postgres "-D" "/var/lib/pgsql/13/data/"
[postgres@postgres data]$ ps -ef | grep postgres
avahi 1182 1 0 17:41 ? 00:00:00 avahi-daemon: running [postgres.local]
postgres 1382 1 0 17:41 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres 1406 1382 0 17:41 ? 00:00:00 postgres: logger
postgres 1408 1382 0 17:41 ? 00:00:00 postgres: checkpointer
postgres 1409 1382 0 17:41 ? 00:00:00 postgres: background writer
postgres 1410 1382 0 17:41 ? 00:00:00 postgres: walwriter
postgres 1411 1382 0 17:41 ? 00:00:00 postgres: autovacuum launcher
postgres 1412 1382 0 17:41 ? 00:00:00 postgres: stats collector
postgres 1413 1382 0 17:41 ? 00:00:00 postgres: logical replication launcher
pcp 2637 1 1 17:41 ? 00:01:54 /usr/bin/pmie -b -F -P -l /var/log/pcp/pmie/postgres/pmie.log -c config.default
root 39088 38885 0 19:35 pts/0 00:00:00 su - postgres
postgres 39095 39088 0 19:35 pts/0 00:00:00 -bash
postgres 40160 39095 0 19:56 pts/0 00:00:00 ps -ef
postgres 40161 39095 0 19:56 pts/0 00:00:00 grep --color=auto postgres
[postgres@postgres data]$
5. 进行杀进程
[postgres@postgres data]$ ps -ef | grep postgres
avahi 1182 1 0 17:41 ? 00:00:00 avahi-daemon: running [postgres.local]
postgres 1382 1 0 17:41 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres 1406 1382 0 17:41 ? 00:00:00 postgres: logger
postgres 1408 1382 0 17:41 ? 00:00:00 postgres: checkpointer
postgres 1409 1382 0 17:41 ? 00:00:00 postgres: background writer
postgres 1410 1382 0 17:41 ? 00:00:00 postgres: walwriter
postgres 1411 1382 0 17:41 ? 00:00:00 postgres: autovacuum launcher
postgres 1412 1382 0 17:41 ? 00:00:00 postgres: stats collector
postgres 1413 1382 0 17:41 ? 00:00:00 postgres: logical replication launcher
pcp 2637 1 1 17:41 ? 00:01:54 /usr/bin/pmie -b -F -P -l /var/log/pcp/pmie/postgres/pmie.log -c config.default
root 39088 38885 0 19:35 pts/0 00:00:00 su - postgres
postgres 39095 39088 0 19:35 pts/0 00:00:00 -bash
postgres 40160 39095 0 19:56 pts/0 00:00:00 ps -ef
postgres 40161 39095 0 19:56 pts/0 00:00:00 grep --color=auto postgres
[postgres@postgres data]$
[postgres@postgres data]$ kill -9 1409
[postgres@postgres data]$ ps -ef | grep postgres
avahi 1182 1 0 17:41 ? 00:00:00 avahi-daemon: running [postgres.local]
postgres 1382 1 0 17:41 ? 00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres 1406 1382 0 17:41 ? 00:00:00 postgres: logger
pcp 2637 1 1 17:41 ? 00:01:54 /usr/bin/pmie -b -F -P -l /var/log/pcp/pmie/postgres/pmie.log -c config.default
root 39088 38885 0 19:35 pts/0 00:00:00 su - postgres
postgres 39095 39088 0 19:35 pts/0 00:00:00 -bash
postgres 40400 1382 0 19:58 ? 00:00:00 postgres: checkpointer
postgres 40401 1382 0 19:58 ? 00:00:00 postgres: background writer
postgres 40402 1382 0 19:58 ? 00:00:00 postgres: walwriter
postgres 40403 1382 0 19:58 ? 00:00:00 postgres: autovacuum launcher
postgres 40404 1382 0 19:58 ? 00:00:00 postgres: stats collector
postgres 40405 1382 0 19:58 ? 00:00:00 postgres: logical replication launcher
postgres 40406 39095 0 19:58 pts/0 00:00:00 ps -ef
postgres 40407 39095 0 19:58 pts/0 00:00:00 grep --color=auto postgres
[postgres@postgres data]$
6.查看数据库状态,使用psql登录postgres数据库
[postgres@postgres data]$ pg_ctl status
pg_ctl: server is running (PID: 1382)
/usr/pgsql-13/bin/postgres "-D" "/var/lib/pgsql/13/data/"
[postgres@postgres data]$
[postgres@postgres data]$ psql
psql (13.9)
Type "help" for help.
postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/13/data
(1 row)
postgres=# show port;
port
------
5432
(1 row)
postgres=# \q
7.停止数据库
[postgres@postgres data]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@postgres data]$ psql
psql: error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
[postgres@postgres data]$
8.修改系统参数需要重新加载或重启数据库
[postgres@postgres data]$ pg_ctl start
waiting for server to start....2023-04-23 20:00:01.817 HKT [40452] LOG: redirecting log output to logging collector process
2023-04-23 20:00:01.817 HKT [40452] HINT: Future log output will appear in directory "log".
done
server started
[postgres@postgres data]$
[postgres@postgres data]$ psql
psql (13.9)
Type "help" for help.
postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=# alter system set work_mem='10MB';
ALTER SYSTEM
postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
postgres=# exit
[postgres@postgres data]$ psql
psql (13.9)
Type "help" for help.
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show work_mem;
work_mem
----------
10MB
(1 row)
postgres=#
postgres-# \q
[postgres@postgres data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-04-23 20:08:49.920 HKT [40671] LOG: redirecting log output to logging collector process
2023-04-23 20:08:49.920 HKT [40671] HINT: Future log output will appear in directory "log".
done
server started
[postgres@postgres data]$ psql
psql (13.9)
Type "help" for help.
postgres=# exit
9. 查看参数
[postgres@postgres data]$ pg_controldata
pg_control version number: 1300
Catalog version number: 202007201
Database system identifier: 7168448509052422436
Database cluster state: in production
pg_control last modified: Sun 23 Apr 2023 08:08:49 PM HKT
Latest checkpoint location: 0/163D9E8
Latest checkpoint's REDO location: 0/163D9E8
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:487
Latest checkpoint's NextOID: 14387
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 479
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Sun 23 Apr 2023 08:08:49 PM HKT
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 707232009230e91ce0162b17077b5de53b0966a83caef21d0d89b7a962482cfb
[postgres@postgres data]$
10.另一种方式启、停数据库,并查看数据库状态。
[postgres@postgres data]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@postgres data]$ pg_ctl status
pg_ctl: no server running
[postgres@postgres data]$ exit
logout
[pgadmin@postgres ~]$ su - root
Password:
[root@postgres ~]# sytemctl status postgresql-13
bash: sytemctl: command not found...
[root@postgres ~]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Sun 2023-04-23 19:59:41 HKT; 12min ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 1382 ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1382 (code=exited, status=0/SUCCESS)
Nov 21 21:29:00 postgres systemd[1]: Starting PostgreSQL 13 database server...
Nov 21 21:29:00 postgres postmaster[1382]: 2022-11-21 21:29:00.136 HKT [1382] LOG: redirecting log output to logging collector process
Nov 21 21:29:00 postgres postmaster[1382]: 2022-11-21 21:29:00.136 HKT [1382] HINT: Future log output will appear in directory "log".
Nov 21 21:29:00 postgres systemd[1]: Started PostgreSQL 13 database server.
Apr 23 19:59:41 postgres systemd[1]: postgresql-13.service: Killing process 1406 (postmaster) with signal SIGKILL.
Apr 23 19:59:41 postgres systemd[1]: postgresql-13.service: Succeeded.
[root@postgres ~]#
[root@postgres ~]# systemctl start postgresql-13
[root@postgres ~]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2023-04-23 20:12:20 HKT; 3s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 40846 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 40852 (postmaster)
Tasks: 8 (limit: 24548)
Memory: 16.9M
CGroup: /system.slice/postgresql-13.service
├─40852 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─40853 postgres: logger
├─40856 postgres: checkpointer
├─40857 postgres: background writer
├─40858 postgres: walwriter
├─40859 postgres: autovacuum launcher
├─40860 postgres: stats collector
└─40861 postgres: logical replication launcher
Apr 23 20:12:20 postgres systemd[1]: Starting PostgreSQL 13 database server...
Apr 23 20:12:20 postgres postmaster[40852]: 2023-04-23 20:12:20.386 HKT [40852] LOG: redirecting log output to logging collector process
Apr 23 20:12:20 postgres postmaster[40852]: 2023-04-23 20:12:20.386 HKT [40852] HINT: Future log output will appear in directory "log".
Apr 23 20:12:20 postgres systemd[1]: Started PostgreSQL 13 database server.
[root@postgres ~]#
[root@postgres ~]#
[root@postgres ~]# systemctl enable postgresql-13
[root@postgres ~]# systemctl disable postgresql-13
Removed /etc/systemd/system/multi-user.target.wants/postgresql-13.service.
[root@postgres ~]#
[root@postgres ~]#
[root@postgres ~]# systemctl stop postgresql-13
[root@postgres ~]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://www.postgresql.org/docs/13/static/
Nov 21 21:29:00 postgres systemd[1]: Started PostgreSQL 13 database server.
Apr 23 19:59:41 postgres systemd[1]: postgresql-13.service: Killing process 1406 (postmaster) with signal SIGKILL.
Apr 23 19:59:41 postgres systemd[1]: postgresql-13.service: Succeeded.
Apr 23 20:12:20 postgres systemd[1]: Starting PostgreSQL 13 database server...
Apr 23 20:12:20 postgres postmaster[40852]: 2023-04-23 20:12:20.386 HKT [40852] LOG: redirecting log output to logging collector process
Apr 23 20:12:20 postgres postmaster[40852]: 2023-04-23 20:12:20.386 HKT [40852] HINT: Future log output will appear in directory "log".
Apr 23 20:12:20 postgres systemd[1]: Started PostgreSQL 13 database server.
Apr 23 20:14:26 postgres systemd[1]: Stopping PostgreSQL 13 database server...
Apr 23 20:14:26 postgres systemd[1]: postgresql-13.service: Succeeded.
Apr 23 20:14:26 postgres systemd[1]: Stopped PostgreSQL 13 database server.
[root@postgres ~]#