我的PostgreSQL技术笔记

发布时间：2020-12-13 16:59:16 所属栏目：百科来源：网络整理

导读：0、安装PostgreSQL服务器若有不明之处，参见官方手册： https://www.postgresql.org/docs/9.4/static/runtime.html 权限配置，参见： https://www.postgresql.org/docs/9.4/static/client-authentication.html 1、安装 ubuntu直接： apt-get install postgr

0、安装PostgreSQL服务器

若有不明之处，参见官方手册：

https://www.postgresql.org/docs/9.4/static/runtime.html

权限配置，参见： https://www.postgresql.org/docs/9.4/static/client-authentication.html

1、安装

ubuntu直接：

 
 apt-get install postgresql-9.4 

其他参见： https://www.postgresql.org/download/

  Get:1http://ftp.cn.debian.org/debian/jessie/mainlibpq5amd649.4.8-0+deb8u1[123kB] 

  Get:2http://ftp.cn.debian.org/debian/jessie/mainpostgresql-client-commonall165+deb8u1[73.7kB] 

  Get:3http://ftp.cn.debian.org/debian/jessie/mainpostgresql-client-9.4amd649.4.8-0+deb8u1[1,073kB] 

  Get:4http://ftp.cn.debian.org/debian/jessie/mainssl-certall1.0.35[20.9kB] 

  Get:5http://ftp.cn.debian.org/debian/jessie/mainpostgresql-commonall165+deb8u1[203kB] 

  Get:6http://ftp.cn.debian.org/debian/jessie/mainpostgresql-9.4amd649.4.8-0+deb8u1[3,687kB] 

  Fetched5,181kBin4s(1,218kB/s) 

  Preconfiguringpackages... 

  Selectingpreviouslyunselectedpackagelibpq5:amd64. 

  (Readingdatabase...34370filesanddirectoriescurrentlyinstalled.) 

  Preparingtounpack.../libpq5_9.4.8-0+deb8u1_amd64.deb... 

  Unpackinglibpq5:amd64(9.4.8-0+deb8u1)... 

  Selectingpreviouslyunselectedpackagepostgresql-client-common. 

  Preparingtounpack.../postgresql-client-common_165+deb8u1_all.deb... 

  Unpackingpostgresql-client-common(165+deb8u1)... 

  Selectingpreviouslyunselectedpackagepostgresql-client-9.4. 

  Preparingtounpack.../postgresql-client-9.4_9.4.8-0+deb8u1_amd64.deb... 

  Unpackingpostgresql-client-9.4(9.4.8-0+deb8u1)... 

  Selectingpreviouslyunselectedpackagessl-cert. 

  Preparingtounpack.../ssl-cert_1.0.35_all.deb... 

  Unpackingssl-cert(1.0.35)... 

  Selectingpreviouslyunselectedpackagepostgresql-common. 

  Preparingtounpack.../postgresql-common_165+deb8u1_all.deb... 

  Adding'diversionof/usr/bin/pg_configto/usr/bin/pg_config.libpq-devbypostgresql-common' 

  Unpackingpostgresql-common(165+deb8u1)... 

  Selectingpreviouslyunselectedpackagepostgresql-9.4. 

  Preparingtounpack.../postgresql-9.4_9.4.8-0+deb8u1_amd64.deb... 

  Unpackingpostgresql-9.4(9.4.8-0+deb8u1)... 

  Processingtriggersforman-db(2.7.0.2-5)... 

  Processingtriggersforsystemd(215-17+deb8u1)... 

  Settinguplibpq5:amd64(9.4.8-0+deb8u1)... 

  Settinguppostgresql-client-common(165+deb8u1)... 

  Settinguppostgresql-client-9.4(9.4.8-0+deb8u1)... 

  update-alternatives:using/usr/share/postgresql/9.4/man/man1/psql.1.gztoprovide/usr/share/man/man1/psql.1.gz(psql.1.gz)inautomode 

  Settingupssl-cert(1.0.35)... 

  Settinguppostgresql-common(165+deb8u1)... 

  Addinguserpostgrestogroupssl-cert 

  Creatingconfigfile/etc/postgresql-common/createcluster.confwithnewversion 

  Creatingconfigfile/etc/logrotate.d/postgresql-commonwithnewversion 

  BuildingPostgreSQLdictionariesfrominstalledmyspell/hunspellpackages... 

  Removingobsoletedictionaryfiles: 

  Settinguppostgresql-9.4(9.4.8-0+deb8u1)... 

  Creatingnewcluster9.4/main... 

  config/etc/postgresql/9.4/main 

  data/var/lib/postgresql/9.4/main 

  localeen_US.UTF-8 

  Flagsof/var/lib/postgresql/9.4/mainsetas-------------e-C 

  port5432 

  update-alternatives:using/usr/share/postgresql/9.4/man/man1/postmaster.1.gztoprovide/usr/share/man/man1/postmaster.1.gz(postmaster.1.gz)inautomode 

  Processingtriggersforlibc-bin(2.19-18+deb8u3)... 

2、修改用户密码

安装时会创建postgres用户，用户的home路径为安装路径下面的一个地方，例如

/var/lib/postgresql

初次使用这个用户，最好是用root修改一下密码：

echopostgres:postgres|chpasswd

3、设置一下环境变量(参见： https://www.postgresql.org/docs/9.4/static/install-post.html)

用postgres用户登录，然后在其主目录执行：

vim~/.bash_profile

增加如下内容：

exportPGHOME=/usr/lib/postgresql/9.4
exportPATH=$PGHOME/bin:$PATH
exportPGDATA=$HOME/data
exportLD_LIBRARY_PATH=$PGHOME/lib
MANPATH=/usr/share/postgresql/9.4/man:$MANPATH
exportMANPATH

其中PGHOME是postgresql的bin和lib所在的目录，可以通过以下命令查看

whereispostgresql

显示：postgresql:/usr/lib/postgresql/etc/postgresql/usr/share/postgresql

第一个路径就是postgresql的安装路径。

注意PGDATA设置的是当前用户的home目录的data目录下，可以先创建这个目录：

mkdir-p~/data

修改配置：

1.PostgresPlus8.3datapg_hba.conf

#IPv4localconnections:

hostallall192.168.0.1/32trust

这样ip为192.168.0.1就可以访问你的数据库了。

如果还是不可以，关掉防火墙

2、更改postgresql.conf下

#listen_addresses='localhost'#whatIPaddress(es)tolistenon;

为

listen_addresses='*'

记得去掉listen_addresses前的#号

4、然后执行初始化data空间

 
 postgres@open-auth-tomcat:~$initdb 

  Thefilesbelongingtothisdatabasesystemwillbeownedbyuser"postgres". 

  Thisusermustalsoowntheserverprocess. 

  Thedatabaseclusterwillbeinitializedwithlocale"en_US.UTF-8". 

  Thedefaultdatabaseencodinghasaccordinglybeensetto"UTF8". 

  Thedefaulttextsearchconfigurationwillbesetto"english". 

  Datapagechecksumsaredisabled. 

  fixingpermissionsonexistingdirectory/var/lib/postgresql/data...ok 

  creatingsubdirectories...ok 

  selectingdefaultmax_connections...100 

  selectingdefaultshared_buffers...128MB 

  selectingdynamicsharedmemoryimplementation...posix 

  creatingconfigurationfiles...ok 

  creatingtemplate1databasein/var/lib/postgresql/data/base/1...ok 

  initializingpg_authid...ok 

  initializingdependencies...ok 

  creatingsystemviews...ok 

  loadingsystemobjects'descriptions...ok 

  creatingcollations...ok 

  creatingconversions...ok 

  creatingdictionaries...ok 

  settingprivilegesonbuilt-inobjects...ok 

  creatinginformationschema...ok 

  loadingPL/pgSQLserver-sidelanguage...ok 

  vacuumingdatabasetemplate1...ok 

  copyingtemplate1totemplate0...ok 

  copyingtemplate1topostgres...ok 

  syncingdatatodisk...ok 

  WARNING:enabling"trust"authenticationforlocalconnections 

  Youcanchangethisbyeditingpg_hba.conforusingtheoption-A,or 

  --auth-localand--auth-host,thenexttimeyouruninitdb. 

 
 Success.Youcannowstartthedatabaseserverusing: 

 
 postgres-D/var/lib/postgresql/data 

or

 
 pg_ctl-D/var/lib/postgresql/data-llogfilestart 

其他启动命令：

postgres-D/var/lib/postgresql/data>logfile2>&1&

关闭命令：

$kill-INT`head-1/var/lib/postgresql/data/postmaster.pid`

其中postmaster.pid的路径为data所在的路径下面

进入sql命令行：

参见： https://www.postgresql.org/docs/9.4/static/tutorial-accessdb.html

  postgres@open-auth-tomcat:~/data$psql-Upostgres-dpostgres 

  psql(9.4.8) 

  Type"help"forhelp. 

  postgres=#help 

  Youareusingpsql,thecommand-lineinterfacetoPostgreSQL. 

 
 Type:copyrightfordistributionterms 

 
 hforhelpwithSQLcommands 

 
 ?forhelpwithpsqlcommands 

 
 gorterminatewithsemicolontoexecutequery 

 
 qtoquit 

 
 postgres=#l 

  Listofdatabases 

  Name|Owner|Encoding|Collate|Ctype|Accessprivileges 

  -----------+----------+----------+-------------+-------------+----------------------- 

 
 postgres|postgres|UTF8|en_US.UTF-8|en_US.UTF-8| 

  template0|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=c/postgres+ 

  |||||postgres=CTc/postgres 

  template1|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=c/postgres+ 

  (3rows) 

 
 postgres=#q 

默认创建了postgres的数据库

可以创建新数据库：根据模板template0创建数据库

CREATEDATABASEsonarqubeTEMPLATEtemplate0;

或者

$ createdb-Ttemplate0sonarqube

修改数据库;

https://www.postgresql.org/docs/9.4/static/sql-alterdatabase.html

创建role：

参见： https://www.postgresql.org/docs/9.4/static/database-roles.html

CREATEROLEsonarqubeLOGIN;

ALTERROLEsonarqubePASSWORD'123456';

注意： postgresql修改密码默认是用的MD5加密过，所以这样的123456的密码明文用不了，如果想设置成明文的123456如下：

ALTERROLEsonarqube UNENCRYPTEDPASSWORD'123456';

设置密码永久有效：ALTERROLEsonarqubeVALIDUNTIL'infinity';

1、安装客户端

安装：

pgadmin3-1.16.1.zip

下载地址http://www.postgresql.org/ftp/pgadmin3/release/v1.16.1/

2、登录报错Access to database denied

详细信息如下：

Access to database denied

The server doesn't grant access to the database: the server reports

FATAL:no pg_hba.conf entry for host "170.12.3.8",user "dcm",database "dcm",SSL off

To access a database on a PostgreSQL server,you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

The initial settings in pg_hba.conf are quite restrictive,in order to avoid unwanted security holes caused by unreviewed but mandatory system settings.You'll probably want to add something like

host all all 192.168.0.0/24 md5

This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.

You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf,you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

解决方案：

在PostgreSQL安装目录下，找到pg_hba.conf文件，例如：/opt/PostgresPlus/9.2AS/data/pg_hba.conf

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all all 127.0.0.1/32 md5

host all all 170.16.0.0/16 md5

host all all 170.16.3.8/32 md5

host all all 10.6.174.10/32 trust

# IPv6 local connections:

host all all ::1/128 md5

# Allow replication connections from localhost,by a user with the replication privilege.

#local replication enterprisedb md5

#host replication enterprisedb 127.0.0.1/32 md5

#host replication enterprisedb ::1/128 md5

增加IP配置项：

host all all 170.26.3.0/24 md5

注意这个24不要乱写，24是子网掩码。

例如：

170.26.0.0/12

这个/12代表子网掩码：/12代表12个二进制1（22222111.11110000.00000000.00000000）

/8: 255.0.0.0

/12: 255.240.0.0

/16: 255.255.0.0

/24: 255.255.255.0

170.26.0.0/12能表示的范围为：

170.00010000.00000000.00000000（170.24.0.0 ）

170.22222111.22222111.22222111（170.255.255.255 ）

所以170.26.3.0/24代表：

170.26.3.0~170.26.3.255

就代表0~255范围的IP地址。

md5代表认证方式。常用的有ident，md5，password，trust，reject。

ident是Linux下PostgreSQL默认的local认证方式，凡是能正确登录服务器的操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库。

password是以明文密码传送给数据库，建议不要在生产环境中使用。

md5是常用的密码认证方式，如果你不使用ident，最好使用md5。

trust是只要知道数据库用户名就不需要密码或ident就能登录，建议不要在生产环境中使用。

reject是拒绝认证。

修改后，最好再执行一下reload命令，使配置生效：

执行：

./pg_ctl reload -D $PGDATA=/opt/PostgresPlus/9.2AS/data

（pg_ctl reload -D $PGDATA / service postgresql reload）

3、导出数据：

【其中dcm为用户名dcm_client为数据库名】

./pg_dump -U dcm dcm_client > /opt/back_data/dcm_client.sql

【导入的数据是copy格式的PostgreSQL才能恢复的数据格式】

导出insert式的SQL数据：

./pg_dump -U dcm--inserts> /opt/back_data/dcm_data.sql dcm_client

其中dcm为用户名dcm_client为数据库名

4、新增列

ALTER TABLE dcm_car_passenger ADD COLUMN etnumber character varying(20); -- --票号

ALTER TABLE dcm_car_passenger ADD COLUMN etissueddate character varying(16); -- --出票时间

ALTER TABLE dcm_car_passenger ADD COLUMN etissuanceoffice character varying(10); -- --出票office

ALTER TABLE dcm_car_passenger ADD COLUMN totalfare character varying(10); -- --总票价

COMMENT ON COLUMN dcm_car_passenger.etnumber IS '--票号';

COMMENT ON COLUMN dcm_car_passenger.etissueddate IS '--出票时间';

COMMENT ON COLUMN dcm_car_passenger.etissuanceoffice IS '--出票office';

COMMENT ON COLUMN dcm_car_passenger.totalfare IS '--总票价';

5、修改列

ALTER TABLEdcm_car_passenger

ALTER COLUMN foid TYPE character varying(30);

COMMENT ON COLUMNdcm_car_passenger.foid IS '---旅客证件号';

PostgreSQL的语法;

https://www.postgresql.org/docs/9.4/static/tutorial-sql.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!