关于postgres 的基础应用

最新推荐文章于 2023-09-24 08:39:51 发布
最新推荐文章于 2023-09-24 08:39:51 发布
阅读量908 收藏 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 ~]#

DB架构
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用pgAdmin连接到Docker中的postgres【报错：Unable to connect to server: connection to server at “localhost”】
qq_34422681的博客
04-09 1318
Unable to connect to server: connection to server at "localhost"(127.0.0.1),port 6700 failed;Connection refused is the server running on that host and accepting TCP/IP connections?connection to server at "localhost"(::1),port 6700 failed;Address not availa
postgres中文文档.pdf
03-30
### PostgreSQL中文文档知识点总结 #### 一、PostgreSQL简介 ...以上是对PostgreSQL中文文档的主要知识点的概述，这些内容覆盖了PostgreSQL的基础到高级的所有方面，对于初学者和高级用户都有很高的参考价值。
PostgreSQL 16 发布，更可靠更稳健
最新发布
IT邦德
09-24 2598
2023年9月14日 - PostgreSQL 全球开发集团重磅宣布发布 PostgreSQL 16，这是世界上最先进的开源数据库的最新版本！
postgresql数据库无法连接，提示 Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP conn
热门推荐
谷歌是检验真理的唯一标准的博客
10-16 3万+
服务 - 点击PostgreSQL database server 8.3 - 点击第二个选项卡“登录” - 选择第一行“本地系统帐户”。
PostgreSQL的locale对排序的影响
my1324的博客
12-06 2518
locale在哪里 在Windows平台的安装过程中，有一步需要选择locale，几个主要选项是Default location``C``Posix``Chinese
  Is the server running on host "localhost" (::1) and accepting         TCP/IP connections on port 5
微电子学与固体电子学-俞驰
03-11 5089
完整报错如下: django.db.utils.OperationalError: could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on por...
最全的关于postgres开发的资料
09-22
这篇文档提供了全面的关于 PostgreSQL 开发的资料，特别是深入探讨了在 Linux 环境下使用 PostgreSQL 的各个方面，包括数据库的结构和字符集配置，这些都是开发者在实际操作中必须了解的关键点。 首先，PostgreSQL ...
PostgresSQL：通过官方文档学习基础知识
02-13
PostgreSQL是一种功能强大的开源对象关系数据库系统，以其稳定性和丰富的特性在IT行业中备受推崇。...在PostgresSQL-main文件中，你可以找到更多关于PL/pgSQL的实际示例和练习，以加深理解和应用。
Postgres-XL集群部署文档2022.docx
03-12
它在Postgres-XC的基础上进行了升级，增强了对OLAP应用的支持。 在Postgres-XL集群中，主要有三种角色的节点：GTM（全局事务管理器）、Coordinator（协调器）和Datanode（数据节点）。GTM负责全局事务的管理和一致...
postgres_sql
07-09
【postgres_sql】是一个关于PostgreSQL数据库管理系统的基础教程，涵盖了SQL语言的重要概念和特性。以下是对这些知识点的详细解释： 1. **WEB-INF目录**：在Web应用中，`WEB-INF`目录下的文件不能直接通过URL访问，...
【PostgreSQL】几个提高性能的小特性
pantouyuchiyu的博客
08-10 1008
在PostgreSQL里，LOCALE默认使用C的本地化规则。LOCALE是一种文化偏好的区域设置，包括字母表、排序、数字格式等。LOCALE里有一个比较重要的规则LC_COLLATE，即排序方式(Collation)，它会对数据库行为有显著影响。
postgresSQL
youqudek的博客
07-05 2769
Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
PostgreSQL安装配置及常见操作
企业实战系列集 ●●● https://ximenjianxue.blog.csdn.net
08-08 550
介绍 官网：https://www.postgresql.org/，最新版本：2020-06-25 - PostgreSQL 13 Beta 2 Released! PostgreSQL是一个功能强大的开源对象关系数据库系统，除了免费和开源之外，PostgreSQL也是高度可扩展的。例如，您可以定义自己的数据类型，许多功能（例如索引）都定义了API，构建自定义函数，甚至可以使用不同的编程语言编写代码，而无需重新编译数据库！从2019年10月的12版开始，PostgreSQL符合179种SQL：2016 C
postgresql连接本地服务器出现报错
qq_39053966的博客
09-09 2616
1、在连接本地服务器的时候出现报错 could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “127.0.0.1” and accepting TCP/IP connections on port 5432? 解决方法 1：查看services.msc中没有postgresql的服务（若是没有服务导致的请按以下步骤，若有服务则启动服务应该就可以解决） 2：注册windo
psql 连不上数据库
Jeffery的专栏
07-17 1071
psql命令终端连不上PostgreSQL数据库
Postgresql-1-解决报错：connection to localhost:5432 refused that the postmaster is accepting TCP/IP
IOT_LI的专栏
08-30 1万+
今天客户那边服务突然访问不了，数据库报错：Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432? 等提示。 该配置的都配置过，连接个数也不多，从“任务管理”-->“服务”里面看到Postgresql 服务已停止，启动不起来。 最终解决办法：...
PostgreSQL vs Oracle checksum 配置与性能
weixin_33971977的博客
07-28 818
标签 PostgreSQL , Oracle , checksum , IO错误 背景 开启数据库block checksum可以发现磁盘、存储、IO系统的问题引入一些的物理错误。 PostgreSQL checksum 在PostgreSQL中，默认强制对XLOG（WAL）开启了checksum，因此可以保证从redo buffer...
Postgrel 数据库常用命令
java2python的博客
08-13 612
CREATE DATABASE dbname; postgres-# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值