2023-11-13

postgresql介绍

install

官网网站

https://www.postgresql.org/download/
https://www.postgresql.org/ftp/source/

centos

先安装PostgreSQL的YUM源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装server
yum install -y postgresql13-server

初始化数据库
/usr/pgsql-13/bin/postgresql-13-setup initdb

PostgreSQL 初始化数据库之后，默认的数据目录是在/var/lib/pgsql

移动原始数据目录
mv /var/lib/pgsql /data/

创建软连
cd /var/lib && ln -s /data/databases/pgsql pgsql

默认密码设置为“postgres”

配置文件

/etc/postgresql/13/main/postgresql.conf

postgresql.conf

关键参数
#connection control
listen_addresses = '*'
max_connections = 2000
superuser_reserved_connections = 10     
tcp_keepalives_idle = 60               
tcp_keepalives_interval = 10         
tcp_keepalives_count = 10        
password_encryption = md5      


#memory management      
shared_buffers = 16GB    #推荐操作系统物理内存的1/4              
max_prepared_transactions = 2000              
work_mem = 8MB                       
maintenance_work_mem = 2GB            
autovacuum_work_mem = 1GB             
dynamic_shared_memory_type = posix      
max_files_per_process = 24800           
effective_cache_size = 32GB   #推荐操作系统物理内存的1/2


#write optimization
bgwriter_delay = 10ms                   
bgwriter_lru_maxpages = 1000            
bgwriter_lru_multiplier = 10.0          
bgwriter_flush_after = 512kB           
effective_io_concurrency = 0          
max_worker_processes = 256             
max_parallel_maintenance_workers = 6   
max_parallel_workers_per_gather = 0     
max_parallel_workers = 28              


#wal optimization
synchronous_commit = remote_write      
full_page_writes = on                 
wal_compression = on                   
wal_writer_delay = 10ms                
wal_writer_flush_after = 1MB           
commit_delay = 10                  
commit_siblings = 5                  
checkpoint_timeout = 30min           
max_wal_size = 32GB
min_wal_size = 16GB
archive_mode = on
max_wal_senders = 64         
wal_keep_segments = 15      
wal_sender_timeout = 60s        
max_replication_slots = 64      
hot_standby_feedback = off            


#log optimization
log_destination = 'csvlog'             
logging_collector = on          
log_directory = '/pg12.4/logs'        # 日志存放路径，提前规划在系统上创建好
log_filename = 'postgresql-%a.log'
log_file_mode = 0600     
log_truncate_on_rotation = on       
log_rotation_age = 1d                 
log_rotation_size = 1GB        


#audit settings
log_min_duration_statement = 5s     
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose         
log_line_prefix = '%m [%p] %q %u %d %a %r %e '       
log_statement = 'ddl'                  
log_timezone = 'PRC'
track_io_timing = on
track_activity_query_size = 2048


#autovacuum
autovacuum = on                         
vacuum_cost_delay = 0                   
old_snapshot_threshold = 6h            
log_autovacuum_min_duration = 0         
autovacuum_max_workers = 8              
autovacuum_vacuum_scale_factor = 0.02   
autovacuum_analyze_scale_factor = 0.01  
autovacuum_freeze_max_age = 1200000000  
autovacuum_multixact_freeze_max_age = 1250000000       
autovacuum_vacuum_cost_delay = 0ms     


#system environment
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.utf8'     
lc_monetary = 'en_US.utf8'     
lc_numeric = 'en_US.utf8'      
lc_time = 'en_US.utf8'         
default_text_search_config = 'pg_catalog.english'

shared_buffers、effective_cache_size、 log_directory

1
2
3

❯ grep -i "hba_file" /etc/postgresql/*/main/postgresql.conf
hba_file = '/etc/postgresql/15/main/pg_hba.conf'	# host-based authentication file

host all all 192.168.122.0/24 scram-sha-256

❯ grep -i "listen_addresses" /etc/postgresql/*/main/postgresql.conf
#listen_addresses = 'localhost'		# what IP address(es) to listen on;
❯ cat /etc/postgresql/15/main/postgresql.conf | grep listen_addresses
#listen_addresses = 'localhost'		# what IP address(es) to listen on;

listen_addresses = ‘*’

debian

❯  sudo apt install postgresql 

❯ sudo -u postgres psql
[sudo] cs 的密码：
psql (15.5 (Debian 15.5-0+deb12u1))

输入 "help" 来获取帮助信息.

postgres=#  ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE

https://www.python100.com/html/115598.html

基本操作

切换数据库,相当于mysql的use dbname

\c dbname

列举数据库，相当于mysql的show databases

\l

列举表，相当于mysql的show tables

\dt

查看表结构，相当于desc tblname,show columns from tbname

1	\d tblname

**查看索引 \di **

timescaledb

仓库 https://github.com/timescale/timescaledb

安装

https://docs.timescale.com/self-hosted/latest/install/installation-linux/

echo "deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list

sudo apt update
sudo  apt install timescaledb-2-postgresql-15

配置

❯ cat /etc/postgresql/15/main/postgresql.conf  | grep shared_preload_libraries
#shared_preload_libraries = ''	# (change requires restart)

❯ sudo sed -i "/^#shared_preload_libraries/a \shared_preload_libraries = 'timescaledb' " /etc/postgresql/15/main/postgresql.conf

❯ cat /etc/postgresql/15/main/postgresql.conf  | grep shared_preload_libraries
#shared_preload_libraries = ''	# (change requires restart)
shared_preload_libraries = 'timescaledb'

❯ sudo systemctl restart postgresql@15-main.service
❯ systemctl status  postgresql@15-main.service
● postgresql@15-main.service - PostgreSQL Cluster 15-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled)
     Active: active (running) since Sun 2024-01-28 19:53:06 CST; 3s ago

postgres=# CREATE database tsdb;
CREATE DATABASE
postgres=# \c
您现在已经连接到数据库 “postgres”,用户 “postgres”.
postgres=# \c tsdb
您现在已经连接到数据库 “tsdb”,用户 “postgres”.
tsdb=# CREATE EXTENSION IF NOT EXISTS timescaledb;
警告:
WELCOME TO

Running version 2.13.1
For more information on TimescaleDB, please visit the following links:

Getting started: https://docs.timescale.com/timescaledb/latest/getting-started

API reference documentation: https://docs.timescale.com/api/latest

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescale.com/timescaledb/latest/how-to-guides/configuration/telemetry.

CREATE EXTENSION
tsdb=# \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
————-+——–+————+—————————————————————————————
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.13.1 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 行记录)