Last active
March 3, 2017 07:20
-
-
Save peterli888/1f73078eaf77dfb41adfc3080d8ff3e6 to your computer and use it in GitHub Desktop.
debian下 pg_upgrade 升级 postgresql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ps -ef|grep postgres | |
sudo apt install postgresql-9.6 | |
sudo /etc/init.d/postgresql stop | |
检查 | |
修改/etc/postgresql/9.5/main/pg_hba.conf,确保本机信任访问 | |
local all postgres peer | |
修改/etc/postgresql/9.6/main/pg_hba.conf,确保本机信任访问 | |
local all postgres peer | |
sudo su postgres | |
cd /tmp 到一个postgres有权限写入的目录 | |
/usr/lib/postgresql/9.6/bin/pg_upgrade -c -p 5431 -P 5432 | |
-b /usr/lib/postgresql/9.4/bin/ -B /usr/lib/postgresql/9.6/bin/ | |
-d /home/liyong/pgdata94 -D /var/lib/postgresql/9.6/main | |
-o ' -c config_file=/etc/postgresql/9.4/main/postgresql.conf' | |
-O ' -c config_file=/etc/postgresql/9.6/main/postgresql.conf' | |
遇到错误如扩展等,需要删除原扩展或者在新的上面安装同样的扩展 | |
升级 | |
/usr/lib/postgresql/9.6/bin/pg_upgrade -v -p 5431 -P 5432 | |
-b /usr/lib/postgresql/9.4/bin/ -B /usr/lib/postgresql/9.6/bin/ | |
-d /home/liyong/pgdata94 -D /var/lib/postgresql/9.6/main | |
-o ' -c config_file=/etc/postgresql/9.4/main/postgresql.conf' | |
-O ' -c config_file=/etc/postgresql/9.6/main/postgresql.conf' | |
升级无误后,exit回到root用户,移除旧的9.5版本 | |
sudo apt remove postgresql-9.5 | |
启动PostgreSQL | |
sudo service postgresql start | |
切换到postges角色 | |
sudo su postgres | |
优化新数据库 | |
"/usr/lib/postgresql/9.6/bin/vacuumdb" --all --analyze-in-stages | |
使用客户端连接新数据库后,无误后,删除旧数据库数据。 | |
rm -rf '/var/lib/postgresql/9.5/main' | |
升级结束 | |
配置文件需要手工修改 /etc/postgresql/9.6/main/* | |
sudo passwd postgres | |
create user liyong with password'liyong'; | |
alter user liyong with password 'liyong'; | |
grant all privileges on database liyong to liyong; | |
drop user liyong; | |
drop extension zhparser; | |
drop function xxx; | |
psql -d liyong -U liyong | |
select current_user | |
select * from pg_authid | |
6 远程访问数据库 | |
postgresql默认情况下,远程访问不能成功,如果需要允许远程访问,需要修改两个配置文件,说明如下: | |
(1)postgresql.conf | |
将该文件中的listen_addresses项值设定为*。 | |
(2)pg_hba.conf | |
在该配置文件的host all all 127.0.0.1/32 md5行下添加以下配置,或者直接将这一行修改为以下配置 | |
host all all 0.0.0.0/0 md5 表示允许所有IP访问。 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment