Summary: Introducing PostgreSQL 14.3 source code installation, postgresql usage and vscode source code debugging.
1. Environment preparation
1.1 System parameter modification
systemctl status firewalld.service #View fire protection status systemctl stop firewalld.service #Temporarily close the firewall systemctl disable firewalld.service #Permanently close the firewall setenforce 0 #Temporarily close selinux firewall, setenforce is the selinux firewall configuration command of Linux sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config #Disable SELinux cat > /etc/sysctl.conf <<"EOF" vm.swappiness=10 #Accessing swap memory is much slower than directly accessing physical memory. A lower value for the swappiness parameter is likely to improve overall system performance. vm.zone_reclaim_mode=0 #Turn off zone_reclaim mode, you can reclaim memory from other zones or NUMA nodes fs.aio-max-nr = 1048576 #The maximum number of asynchronous IO requests allowed in the system fs.file-max = 6815744 #The maximum number of file handles allowed in the system net.ipv4.ip_local_port_range = 9000 65500 #Limitations on the minimum and maximum ports that a network connection can use as its source (local) port net.core.rmem_default = 262144 #Default value of socket receive buffer size net.core.rmem_max = 4194304 #Maximum socket receive buffer size net.core.wmem_default = 262144 #Default value of socket send buffer size net.core.wmem_max = 1048586 #Maximum socket send buffer size kernel.shmmax = 1288490188 #Maximum value of a single shared memory segment kernel.shmall = 314572 #The total amount of available shared memory, the unit is page, generally this value is equal to kernel.shmmax kernel.shmmni = 4096 #Minimum value of a single shared memory segment kernel.sem = 50100 64128000 50100 1280 #Used to control the kernel semaphore EOF sysctl-p cat >> /etc/security/limits.conf <<"EOF" * soft nofile 131072 *hard nofile 131072 * soft nproc 131072 *hard nproc 131072 * soft core unlimited *hard core unlimited * soft memlock 50000000 *hard memlock 50000000 EOF echo "* - nproc unlimited" > /etc/security/limits.d/90-nproc.conf echo "session required pam_limits.so" >> /etc/pam.d/login # Close THP Under root user Add the following code at the end of vi /etc/rc.local if test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi if test -f /sys/kernel/mm/transparent_hugepage/defrag; then echo never > /sys/kernel/mm/transparent_hugepage/defrag fi chmod +x /etc/rc.d/rc.local echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag cat /sys/kernel/mm/transparent_hugepage/enabled cat /sys/kernel/mm/transparent_hugepage/defrag
2. Source code installation
2.1 Download source code package
Download the source package wget https://ftp.postgresql.org/pub/source/v9.32/postgresql-9.2.tar.gz --no-check-certificate
2.2 Install dependency packages
yum install -y cmake make gcc zlib zlib-devel gcc-c + + perl readline readline-devel \ python36 tcl openssl ncurses-devel openldap pam flex
- readline (command line editing support library)
- zlib (data compression support library)
- flex (lexical analysis library)
- bison (grammar analysis library)
2.3 Create user
groupadd postgres useradd postgres echo "postgres" | passwd --stdin postgres chown postgres.postgres /home/postgres/.bash_profile
2.4 Create directory
## Create directory
mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg14,soft}
chown -R postgres:postgres /postgresql
chmod -R 775 /postgresql
? pgdata: data directory
? archive: archive directory
? backup: WAL directory
? pg14: Installation file directory
? soft: source file directory
2.5 Compile and install
--compile su-postgres cd /postgresql/soft tar zxvf postgresql-14.3.tar.gz cd postgresql-14.3 ./configure --prefix=/postgresql/pg14 #or add debugging #./configure --prefix=/postgresql/pg14 --enable-depend --enable-debug --enable-cassert CFLAGS=-O0 make -j 64 & amp; & amp; make install If you want to compile everything that compiles, including documentation (HTML and man pages) and additional modules (contrib), type: make world -j 16 & amp; & amp; make install-world
sudo /sbin/ldconfig /postgresql/pg14/lib
2.6 Configuring environment variables
-- Configure environment variables cat >> $HOME/.bash_profile <<"EOF" exportPGPORT=5432 export PGDATA=/postgresql/pgdata export PGHOME=/postgresql/pg14 export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH:. export PGHOST=$PGDATA exportPGUSER=postgres export PGDATABASE=postgres EOF
source $HOME/.bash_profile
2.7 Initializing the database
-- initialization /postgresql/pg14/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
2.8 Modify parameters
--Modify parameters cat >> /postgresql/pgdata/postgresql.conf <<"EOF" listen_addresses = '*' port=5432 unix_socket_directories='/postgresql/pgdata' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on EOF
2.9 Start database
-- start pg_ctl start pg_ctl status pg_ctl stop pg_ctl stop -m fast
3. Connect to database
su-postgres psql -p 5432 -h 127.0.0.1; //Change database password alter user postgres with password 'postgres';
4. Install plug-in
--Install plugin create extension pageinspect; create extension pg_stat_statements; select * from pg_extension; select * from pg_available_extensions order by name;
? When installing the PG database, a database user with the same name as the operating system user name when initializing the database will be created. At the same time, this user is the super user of the database. Login to the database under this OS user uses operating system authentication, so there is no need user name and password. You can also require a password by modifying the pg_hba.conf file.
cat > /postgresql/pgdata/pg_hba.conf << EOF # TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 EOF
4. Error handling
4.1 socket link error
[postgres@iZuf6ddpzz3ipktm5kj01cZ pgdata]$ psql psql: error: connection to server on socket "/postgresql/pgdata/.s.PGSQL.5433" failed: FATAL: no pg_hba.c onf entry for host "[local]", user "postgres", database "postgres", no encryption
Specify the IP address and port number of postgresql
psql -p 5433 -h 47.103.xxx.xxx;
4.2 Forgot password when connecting to postgres server
[postgres@iZuf6ddpzz3ipktm5kj01cZ ~]$ psql -p 5433 -h 47.103.136.252; Password for user postgres: psql: error: connection to server at "47.103.xxx.xxx", port 5433 failed: FATAL: password authentication failed for user "postgres"
? Modify the METHOD field of /postgresql/pgdata/pg_hba.conf to trust
# TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 trust
?Reset password
postgres=# \password Enter new password: Enter it again:
Restart the server
pg_ctl reload
5. vscode source code debugging
? Add new configurations to launch.json. There are three examples in total, one for postgres –help, one for initdb, and one for postgres backend debugging.
{<!-- -->
"version": "0.2.0",
"configurations": [
{<!-- -->
"name": "postgres --help",
"type": "cppdbg",
"request": "launch",
"program": "/postgresql/pg14/bin/postgres",
"args": [
"--help"
],
"stopAtEntry": false,
"cwd": "${fileDirname}",
"environment": [],
"externalConsole": false,
"MIMode": "gdb",
"miDebuggerArgs": "--command=/home/postgres/cmd.gdb",
"setupCommands": [
{<!-- -->
"description": "Enable pretty-printing for gdb",
"text": "-enable-pretty-printing",
"ignoreFailures": true
}
]
},
{<!-- -->
"name": "initdb",
"type": "cppdbg",
"request": "launch",
"program": "/postgresql/pg14/bin/initdb",
"args": [
"-D",
"<datadir>"
],
"stopAtEntry": false,
"cwd": "${fileDirname}",
"environment": [],
"externalConsole": false,
"MIMode": "gdb",
"miDebuggerArgs": "--command=/home/postgres/cmd.gdb",
"setupCommands": [
{<!-- -->
"description": "Enable pretty-printing for gdb",
"text": "-enable-pretty-printing",
"ignoreFailures": true
}
]
},
{<!-- -->
"name": "postgres backend",
"type": "cppdbg",
"request": "attach",
"program": "/postgresql/pg14/bin/postgres",
"processId": "${command:pickProcess}",
"stopAtEntry": false,
"MIMode": "gdb",
"miDebuggerArgs": "--command=/home/postgres/cmd.gdb",
"setupCommands": [
{<!-- -->
"description": "Enable pretty-printing for gdb",
"text": "-enable-pretty-printing",
"ignoreFailures": true
}
]
}
]
}
? Create the /home/postgres/cmd.gdb file and write the file content
set print element 0 set print object on set prin vtbl on set print pretty on hand SIGUSR1 SIGUSR2 SIG36 SIGSTOP noprint nostop
? Break the point in the exec_simple_query function, then select postgres backend in the sidebar Run and Debug, click the debug button or press F5, enter postgres, and select the postgres db xxxx idle process.
6. Reference
https://cloud.tencent.com/developer/article/2013907