关于postgres 的基础应用

最新推荐文章于 2025-02-21 01:15:37 发布
最新推荐文章于 2025-02-21 01:15:37 发布
阅读量1k 收藏 1
点赞数
分类专栏： Postgres 文章标签： 数据库 postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011868279/article/details/130314071
版权

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 ~]#

最全的关于postgres开发的资料
09-22
这篇文档提供了全面的关于 PostgreSQL 开发的资料，特别是深入探讨了在 Linux 环境下使用 PostgreSQL 的各个方面，包括数据库的结构和字符集配置，这些都是开发者在实际操作中必须了解的关键点。 首先，PostgreSQL ...
nwen304-postgres-heroku:带有 postgres 后端的简单 Web 应用程序示例
06-06
这是一个关于使用 PostgreSQL 数据库作为后端的简单 Web 应用程序示例，部署在 Heroku 平台上。这个项目可能是为了展示如何将 JavaScript 与 PostgreSQL 集成，以及如何在云端环境中运行应用程序。 首先，...
【PostgreSQL】从零开始:（九）PostgreSQL-SQL语句操作数据库
圈小圈DBA的博客
12-17 328
Oracle中数据库实例与数据库是一对一的关系PostgreSQL和MySQL中一个数据库实例对应多个数据库。
PostgreSQL的locale对排序的影响
my1324的博客
12-06 2770
locale在哪里 在Windows平台的安装过程中，有一步需要选择locale，几个主要选项是Default location``C``Posix``Chinese
PostgreSQL-01-入门篇-简介
最新发布
m0_67401134的博客
02-21 1363
相比之下，PostgreSQL 采用了类似于 MIT 的许可协议，允许开发人员在开源或闭源产品中自由使用，这使得 PostgreSQL 成为了一种极具吸引力的选择。1990 年 6 月发布的版本 2 包含了重新设计的规则系统。Postgres95 的开发重点在于识别和解决后端代码的问题，而 PostgreSQL 的开发则转向了更具争议性的特性和功能，尽管这两个方面的工作同时进行。随着外部用户社区的增长，代码维护的时间成本增加，为了减少支持负担，伯克利的 POSTGRES 项目在版本 4.2 时正式终止。
PostgreSQL 16 发布，更可靠更稳健
IT邦德
09-24 3513
2023年9月14日 - PostgreSQL 全球开发集团重磅宣布发布 PostgreSQL 16，这是世界上最先进的开源数据库的最新版本！
PostgreSQL自带的命令行工具21- initdb
文牧之的博客
05-12 1930
是 PostgreSQL 中的一个工具，用于创建一个新的 PostgreSQL 数据库集群。数据库集群是一组数据库和一些与这些数据库相关的初始化信息，包括配置文件（如和）以及事务日志等。实际上，initdb主要负责初始化数据库系统的文件系统环境。
initdb 简介
weixin_30553837的博客
03-15 508
os:centos 6.8 postgresql:10.3 查看initdb的参数 $ /usr/pgsql-10/bin/initdb --help initdb initializes a PostgreSQL database cluster. Usage: initdb [OPTION]... [DATADIR] Opti...
利用Spring Boot, JOOQ和Docker部署Postgres数据库应用
在本篇内容中，我们将深入探讨如何利用Spring Boot、JOOQ以及Docker技术，集成Postgres数据库来构建一个现代的Java后端应用。该教程将涵盖Spring Boot框架的使用、JOOQ库的应用以及如何在Docker容器中部署Postgres...
postgres中文文档.pdf
03-30
### PostgreSQL中文文档知识点总结 #### 一、PostgreSQL简介 ...以上是对PostgreSQL中文文档的主要知识点的概述，这些内容覆盖了PostgreSQL的基础到高级的所有方面，对于初学者和高级用户都有很高的参考价值。
sgro-jpfp:一个网站，展示了使用NodeJS，Express，PostGres和React-Redux构建的完整CRUD应用程序的基础
04-02
这是一个跟踪学生及其相关校园的应用程序。 到目前为止，这基本上是我们在FullStack Academy中学到的所有知识的结合体。 特征 有趣的主屏幕 完整的校园和学生列表屏幕 增加校园和学生 更新校园和学生及其协会 删除...
【PostgreSQL】几个提高性能的小特性
pantouyuchiyu的博客
08-10 1130
在PostgreSQL里，LOCALE默认使用C的本地化规则。LOCALE是一种文化偏好的区域设置，包括字母表、排序、数字格式等。LOCALE里有一个比较重要的规则LC_COLLATE，即排序方式(Collation)，它会对数据库行为有显著影响。
【PostgreSQL】从零开始:（六）PostgreSQL-数据库目录文件结构及作用说明
圈小圈DBA的博客
12-16 1309
总体上，FSM采用3-4级多叉树的结构组织FSM page，单个FSM page内部采用完全二叉树的结构进行管理，高级别FSM page的叶子节点关联低级别的FSM page，低级别FSM Page的叶子节点存储着heap、index page的可用空间数目，而非叶子结点依次存储叶子节点的最大可用空间数目，每个节点占用1个字节。PG 10及之后的高版本改目录名为pg_xact，10之前目录名称是pg_clog。1.首先，扫描WAL段文件所写入的目录，并发现最新完成的段文件，作为下一个段文件的开始的起始点。
PostgreSQL入门和高维向量索引
黄规速博客:学如逆水行舟，不进则退
12-01 2670
一、安装 1.添加PosgreSQL Yum Repository wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-ppc64le/pgdg-centos96-9.6-3.noarch.rpm yum install pgdg-centos96-9.6-3.noarch.rpm ; 2
PostgreSQL 默认字符集问题
一只努力的微服务
08-02 7701
initdb: invalid locale settings 实验1： PostgreSQL在安装后需要初始化data，在用源码编译安装后，通过initdb初始化时出现了： initdb: invalid locale settings; check LANG and LC_* environment variables 导致不能初始化。 实验2： 使用rpm包安装后，通过postgresql...
Ubuntu Server 16.04配置PostgresSQL-9.5的locale使用zh_CN
weixin_34321977的博客
11-21 322
2019独角兽企业重金招聘Python工程师标准>>> ...
PostgreSQL安装配置及常见操作
企业实战系列集 ●●● https://ximenjianxue.blog.csdn.net
08-08 777
介绍 官网：https://www.postgresql.org/，最新版本：2020-06-25 - PostgreSQL 13 Beta 2 Released! PostgreSQL是一个功能强大的开源对象关系数据库系统，除了免费和开源之外，PostgreSQL也是高度可扩展的。例如，您可以定义自己的数据类型，许多功能（例如索引）都定义了API，构建自定义函数，甚至可以使用不同的编程语言编写代码，而无需重新编译数据库！从2019年10月的12版开始，PostgreSQL符合179种SQL：2016 C
【PostgreSQL】从零开始:（七）PostgreSQL-Initdb命令初始化数据库
圈小圈DBA的博客
12-16 3096
initdb是PostgreSQL初始化数据库的工具，用来生成PostgreSQL的Data目录结构使用initdb创建数据库集群时，会自动初始化区域设置支持。initdb默认情况下将使用其执行环境的区域设置来初始化数据库集群，因此，如果您的系统已设置为在数据库集群中使用您想要的区域设置，那么您不需要其他任何东西去做。如果您想使用不同的区域设置（或者您不确定系统设置为哪个区域设置），您可以通过指定initdb使用哪个区域设置4>选项。--locale。
PostgreSQL介绍
孙霸天的专栏
06-12 5660
PostgreSQL 参考地址：https://github.com/digoal/blog/blob/master/201801/20180121_01.md 介绍 PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。 PostgreSQL 开发者把它念作 post-gress-Q-L。 PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。 数据库定位 OLTP：(增删改)（on-line transact
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值