Postgresql

昨天设计了一个quota表，后端代码这样一个初始化的逻辑： 如果quota表为空，则插入一条默认的记录。 上线调试的时候，发现会报错quotas_pkey duplicated，而且只会报一次， 重启后端应用不会再报错。 但是清空table之后，重启后端会再报同样的错。 下面是表结构： 1 2 3 4 5 6 7 8 CREATE TABLE IF NOT EXISTS quotas ( id SERIAL PRIMARY KEY NOT NULL, concurrent smallint NOT NULL DEFAULT 15, total smallint NOT NULL DEFAULT 1000, speed smallint NOT NULL DEFAULT 200, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); 排查一段时间后发生，是下面一段sql代码的问题： 1 2 -- name: InitQuota :exec INSERT INTO quotas (id) VALUES (1); 这段代码想实现的是插入一条id为1的默认记录。后端是做了判断的，只有quotas表为空的时候才执行这条sql语句。 ...

updated: 好像是因为我安装了ripgrep所以会一直更新cargo-c依赖。 不知道为啥每次sudo port -v upgrade outdated 都会重新安装cargo-c， 进而会安装编译rust。 编译rust很费时间和CPU风扇。 所以我就卸载了rust和一众依赖，后面特意又卸载了cargo-c。但是每次upgrade outdated cargo-c又回来了，很是烦人。 google一圈后，决定重新安装macport 清理安装包 尝试clean 所有的安装包： 1 2 3 4 5 6 7 8 9 10 11 12 sudo port uninstall cargo-c sudo port -v selfupdate sudo port -f clean --all all sudo rm -rf /opt/local/var/macports/packages/* sudo rm -rf /opt/local/var/macports/distfiles/* sudo rm -rf /opt/local/var/macports/build/* port echo leaves sudo port uninstall leaves sudo port -f uninstall inactive ## SURPRISE! after upgrade, `cargo-c` come back. sudo port upgrade outdated 卸载macport 参考官网协助步骤： ...

客户需要部署一套 moodle 教学系统。 去moodle官网大致看了一圈，发现moodle 是一个典型的PHP web应用。 其实这种LAMP (Linux, Apache, MySQL, PHP/Perl/Python)的应用， 我一般会用docker componse快速部署的，比如这个docker componse看上去就很不错。 但是客户不想用docker，要求直接在vm上部署。 初步确认部署环境为： nginx(let's encrypt) + php 7.2 + pg 10 + Centos 7.4 。 安装软件 初始化主机 1 2 3 4 5 6 7 8 9 10 hostnamectl set-hostname deoops.com # disable passwd login; use ssh-key only vi /etc/ssh/sshd_config yum update -y yum upgrade -y init 6 # add remi repo rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm 安装nginx 1 2 3 4 5 6 7 8 9 10 11 yum install nginx yum -y install yum-utils yum-config-manager --enable rhui-REGION-rhel-server-extras rhui-REGION-rhel-server-optional ## 安装let's encrypt certbot yum install certbot-nginx systemctl enable nginx systemctl start nginx ## 签发证书 certbot --nginx certonly ls -alh /etc/nginx/ 安装php dependency 1 yum --enablerepo=remi,remi-php72 install php-fpm php-common php-opcache php-pecl-apcu php-cli php-pear php-pdo php-mysqlnd php-pgsql php-pecl-mongodb php-pecl-redis php-pecl-memcache php-pecl-memcached php-gd php-mbstring php-mcrypt php-xml 配置nginx + php-fpm 详细的配置内容看这里 ...

工作需要定时备份postgresql slave数据库数据数据，服务器上运行了两个slave实例，隶属于两个不同的master。 备份 两个slave server实例分别监听在 5432和 4432端口 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 #!/bin/bash # # Daily PostgreSQL maintenance: vacuuming and backuping. # ## set -e for port in 5432 4432; do BACKDIR="/data/pg_back/$port" [ -d $BACKDIR ] || mkdir -p $BACKDIR echo "[`date`] begin Maintaining pg on port $port" # no need to use -U option for DB in $(psql -l -t -p $port |awk '{ print $1}' |grep -vE '^-|:|^List|^Name|template[0|1]|postgres|\|'); do ### swith form 'awk and grep' hacks to psql options and 'select sql' ### which is more dbaer professioner :) for DB in $(psql -AqXtc 'SELECT datname FROM pg_database WHERE datistemplate = false;'); do echo " [`date`] Maintaining $DB" PREFIX="$BACKDIR/$DB" # NO need to do `vacuum` on slaves # do `vacunm` on master instead # echo 'VACUUM' | psql -U postgres -hlocalhost -d $DB DUMP="$PREFIX.`date '+%Y%m%d'`.sql.gz" # no need for -U postgres option pg_dump -p $port $DB | gzip -c > $DUMP PREV="$PREFIX.`date -d'1 day ago' '+%Y%m%d'`.sql.gz" # md5sum -b $DUMP > $DUMP.md5 md5=($(md5sum -b $DUMP)) echo $md5 > $DUMP.md5 if [ -f $PREV.md5 ] && diff $PREV.md5 $DUMP.md5; then rm -f $PREV $PREV.md5 fi ## delete too old backup TOOOLD="$PREFIX.`date -d'15 day ago' '+%Y%m%d'`.sql.gz" [ -f $TOOOLD ] || rm -f $TOOOLD done echo "[`date`] Maintain pg on port $port finished" done 参考：Automatic Offsite PostgreSQL Backups Without a Password ...

一般postgres高可用集群是一个master配一个slave，但是开发这边需要做db的读写分离，所以运维这边 又添加了一台slave专门暴露出来做读操作。原来的slave还是只做备份。 HA 一主一从高可用的配置可以参考下面这篇文章 postgres streaming replication，有时间的话我可能会搬运一下 :) 安装配置 因为pg数据库集群已经配置好了一主一从，所以在master主机上不需要配置pg_hba.conf, 或者CREATE ROLE等等。 添加第二个slave需要注意以下两点： 等待pg_basebackupreplicas stream数据同步完成后，再启动 postgresql-9.6 service; 修改PG_DATA_DIR目录的权限; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 rm /etc/yum.repos.d/pgdg-96-redhat.repo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm yum install -y postgresql96 yum install -y postgresql96-server yum install -y postgresql96-contrib vi /usr/lib/systemd/system/postgresql-9.6.service mkdir /data/pg9.6 chown postgres:postgres /data/pg9.6/ ls -alh /data/pg9.6/ pg_basebackup --help ## you can add option --checkpoint=fast for an instance backup ## qhich is not recommend pg_basebackup -X stream -D /data/pg9.6/ -P -R -h 10.3.3.3 -U replicator ls /data/pg9.6/ cat /data/pg9.6/recovery.conf vi /data/pg9.6/postgresql.conf pwd systemctl start postgresql-9.6.service ls -alh /data/pg9.6/ chown -R postgres:postgres /data/pg9.6 chmod 700 /data/pg9.6 systemctl start postgresql-9.6.service netstat -nlp | grep 5432 su - postgres systemctl enable postgresql-9.6.service check 在master主机上查看pg_stat_replication表数据，验证第二个slave是否正常工作： ...

跟着rails tutorial 学习rails框架时，遇到了db链接的问题 问题 1 2 3 4 5 rake db:create failed PG::ConnectionBad: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"? Google之后发现是database.yml配置文件没有加上host：localhost配置项。 过一会，发现PATH没有包含psql命令。 1 2 3 4 5 6 7 vi ~/.bash_profile #添加下面一行内容 export PATH="/Applications/Postgres.app/Contents/Versions/9.4/bin:$PATH" exit ## or echo export PATH="/Applications/Postgres.app/Contents/Versions/9.4/bin:$PATH" >> ~/.zshrc 终于rake db:migrate 成功。 附录 附上database.yml（production环境使用heroku环境变量) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 default: &default adapter: postgresql encoding: unicode # For details on connection pooling, see rails configuration guide # http://guides.rubyonrails.org/configuring.html#database-pooling pool: 5 development: <<: *default host: localhost database: xxx_development test: <<: *default host: localhost database: xxx_test production: <<: *default database: xxx username: xxx password: <%= ENV['xxx_xxx_PASSWORD'] %>