部署WordPress+PostgreSQL

想起来PostgreSQL这么优秀的开源数据库，难道Wordpress不支持吗？于是乎一番搜索，发现Wordpress原生是不支持PostgreSQL的，只能通过插件的形式更换数据库，这个插件名叫PG4WP。

准备工作

首先，备份数据库，并且，导出XML文件，XML文件是用来在新安装的wordpress中导入的，因为mysql导出的SQL，无法直接用到PostgreSQL的。

导出完成后，reinstall操作系统吧，装好后，可以使用oneinstack之类的脚本，或者，自己安装PostgreSQL，php，nginx这些，还比脚本之类的快一点，效率为上，直接手工apt安装即可。

域名提前做好解析。

1. 安装必需的库

apt install curl wget gnupg2 ca-certificates lsb-release socat git

2. 安装PostgreSQL

# 引入postgreSQL官方源 sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # 导入密钥签名: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - # 更新软件包库: apt-get update # 安装PostgreSQL. # 如果要安装特定版本，那就用'postgresql-12' 或者 'postgresql': apt-get -y install postgresql

3. 修改PostgreSQL本地认证

vi /etc/postgresql/12/main/pg_hba.conf //对于local的认证修改为md5 local all all md5

4. 启动数据库

systemctl enable postgresql systemctl start postgresql

5.安装php

apt install php7.3 php7.3-curl php7.3-fpm php7.3-gd php7.3-json php7.3-mbstring \ php7.3-mysql php7.3-opcache php7.3-pgsql php7.3-xml php7.3-xmlrpc php7.3-zip

6. 安装nginx

# 添加nginx官方源 echo "deb http://nginx.org/packages/debian `lsb_release -cs` nginx" \ | tee /etc/apt/sources.list.d/nginx.list # 导入源签名密钥 curl -fsSL https://nginx.org/keys/nginx_signing.key | apt-key add - # 更新软件仓库 apt update # 安装nginx apt install nginx

7. 安装acme.sh

curl https://get.acme.sh | sh

8. 生成dhparam文件

mkdir /etc/nginx/ssl openssl dhparam -out /etc/nginx/ssl/dhparam.pem 2048

9. 创建普通用户

useradd -m -d /web www mkdir /web/{wwwroot,wwwlogs} chown -R www.www /web

10. 修改配置文件

nginx配置文件

user www www; worker_processes auto; worker_cpu_affinity auto; error_log /var/log/nginx/nginx_error.log crit; pid /var/run/nginx.pid; #Specifies the value for maximum file descriptors that can be opened by this process. worker_rlimit_nofile 51200; events { use epoll; worker_connections 51200; multi_accept off; accept_mutex off; } http { include mime.types; default_type application/octet-stream; server_names_hash_bucket_size 128; client_header_buffer_size 32k; large_client_header_buffers 4 32k; client_max_body_size 50m; sendfile on; sendfile_max_chunk 512k; tcp_nopush on; keepalive_timeout 60; tcp_nodelay on; fastcgi_connect_timeout 300; fastcgi_send_timeout 300; fastcgi_read_timeout 300; fastcgi_buffer_size 64k; fastcgi_buffers 4 64k; fastcgi_busy_buffers_size 128k; fastcgi_temp_file_write_size 256k; gzip on; gzip_min_length 1k; gzip_buffers 4 16k; gzip_http_version 1.1; gzip_comp_level 2; gzip_types text/plain application/javascript application/x-javascript text/javascript text/css application/xml application/xml+rss; gzip_vary on; gzip_proxied expired no-cache no-store private auth; gzip_disable "MSIE [1-6]\."; #limit_conn_zone $binary_remote_addr zone=perip:10m; ##If enable limit_conn_zone,add "limit_conn perip 10;" to server section. server_tokens off; access_log off; include /etc/nginx/conf.d/*.conf; }

php.conf

vi /etc/nginx/php.conf

location ~ [^/]\.php(/|$) { fastcgi_pass unix:/run/php/php7.3-fpm.sock; fastcgi_index index.php; include fastcgi_params; fastcgi_split_path_info ^(.+?\.php)(/.*)$; set $path_info $fastcgi_path_info; fastcgi_param PATH_INFO $path_info; try_files $fastcgi_script_name =404; }

/etc/nginx/fastcgi_params文件

新增一行在最后一行

fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;

php-fpm

编辑/etc/php/7.3/fpm/pool.d/www.conf文件，使用如下配置即可

[www] listen = /run/php/php7.3-fpm.sock listen.backlog = -1 listen.allowed_clients = 127.0.0.1 listen.owner = www listen.group = www listen.mode = 0666 user = www group = www pm = dynamic pm.max_children = 10 pm.start_servers = 2 pm.min_spare_servers = 1 pm.max_spare_servers = 6 pm.max_requests = 1024 pm.process_idle_timeout = 10s request_terminate_timeout = 100 request_slowlog_timeout = 0 slowlog = /var/log/slow.log

8. 启动nginx，php-fpm

systemctl enable php7.3-fpm systemctl enable nginx systemctl restart php7.3-fpm systemctl restart nginx

9. 签发ssl证书

source .bashrc acme.sh --issue -d www.mydomain.com --standalone

如无意外，应该会看到签发的证书的具体位置，例如/root/.acme.sh/www.mydomain.com/fullchain.cer和/root/.acme.sh/www.mydomain.com/www.mydomain.com.key。但是我们还需要将证书安装到nginx的ssl目录，而不是直接使用上面目录的证书，会有权限问题，再者目录会发生变化。

acme.sh --install-cert -d www.example.com \ --key-file /etc/nginx/ssl/www.mydomain.com.key \ --fullchain-file /etc/nginx/ssl/fullchain.cer \ --reloadcmd "service nginx force-reload"

10. 给nginx增加虚拟主机

新建配置文件 vi /etc/nginx/conf.d/mydomain.conf

server { listen 80; server_name www.mydomain.com mydomain.com; return 301 https://www.mydomain.com$request_uri; } server { listen 443 ssl http2; #listen [::]:443 ssl http2; server_name www.mydomain.com mydomain.com; index index.html index.htm index.php default.html default.htm default.php; root /web/wwwroot/www.mydomain.com; location / { try_files $uri $uri/ /index.php?$args; index index.php; } ssl_certificate /etc/nginx/ssl/fullchain.cer; ssl_certificate_key /etc/nginx/ssl/www.mydomain.com.key; ssl_session_timeout 5m; ssl_protocols TLSv1 TLSv1.1 TLSv1.2 TLSv1.3; ssl_prefer_server_ciphers on; ssl_ciphers "TLS13-AES-256-GCM-SHA384:TLS13-CHACHA20-POLY1305-SHA256:TLS13-AES-128-GCM-SHA256:TLS13-AES-128-CCM-8-SHA256:TLS13-AES-128-CCM-SHA256:EECDH+CHACHA20:EECDH+CHACHA20-draft:EECDH+AES128:RSA+AES128:EECDH+AES256:RSA+AES256:EECDH+3DES:RSA+3DES:!MD5"; ssl_session_cache builtin:1000 shared:SSL:10m; ssl_dhparam /etc/nginx/ssl/dhparam.pem; #error_page 404 /404.html; # Deny access to PHP files in specific directory location ~ /(wp-content|uploads|wp-includes|images)/.*\.php$ { deny all; } include php.conf; location ~ .*\.(gif|jpg|jpeg|png|bmp|swf)$ { expires 30d; } location ~ .*\.(js|css)?$ { expires 12h; } location ~ /.well-known { allow all; } location ~ /\. { deny all; } access_log /web/wwwlogs/mydomain.log; }

11. 测试虚拟主机是否工作正常

新建一个phpinfo测试脚本vi /web/wwwroot/www.mydomain.com/t.php

<?php3 phpinfo(); ?>

用浏览器打开 http://www.mydomain.com/t.php 验证

• 是否如我们期待跳转到https
• 是否能解析php脚本

如无意外，你应该能看到地址栏的小锁子了，并且PHP成功解析。

12. 为Wordpress创建数据库

su - postgres psql postgres=# create database your_database; postgres=# create user your_user with password ‘your_password’; postgres=# grant all privileges on database your_database to your_user; postgres=# \q

OK. 至此数据已经成功创建了。现在你就可以试试登录数据库。

psql -U your_user -W Password: psql (12.4 (Debian 12.4-1.pgdg100+1)) Type "help" for help. your_database=> your_database=> \q

13. 下载最新的wordpress版本

cd /web/wwwroot/www.mydomain.com wget https://wordpress.org/latest.tar.gz -O - | tar -zvx mv wordpress/* ./ rm -fr wordpress

14. 安装pg4wg插件

cd /web/wwwroot/www.mydomain.com/wp-content git clone https://github.com/gmercey/PG4WP.git cp PG4WP/db.php ./db.php

15. 调整目录和文件的权限

因为nginx和php-fpm都是以www用户权限跑的，所以需要将web目录下的文件和目录，调整权限，否则无法安装，更新插件，主题等。

chown -R www.www /web

16.配置wordpress数据库信息

默认会有一份wp-config-sample.php的文件，cp一份作为我们配置的起点。

vi wp-config-sample.php wp-config.php

修改如下内容：

/** The name of the database for WordPress */ define('DB_NAME', 'your PostgreSQL database name will go here'); /** MySQL database username */ define('DB_USER', 'your PostgreSQL database username will go here'); /** MySQL database password */ define('DB_PASSWORD', 'your PostgreSQL database password will go here'); /** MySQL hostname */ define('DB_HOST', 'localhost');

17. 安装wordpress

用浏览器打开https://www.mydomain.com/wp-admin/install.php启动wordpress安装程序。

需要注意一下，一定要安装php-mysql 这个扩展，如果不装，wordpress安装开始就报错了。

18. 导入xml

安装完成后，就开始导入吧！

全文结束