postgres linux 目录,PostgreSQL在Linux上的RPM和源码安装

解决方法：[root@rhel6 monchickey]# chmod -R 777 pgsql[root@rhel6 monchickey]# lltotal 8drwxrwxrwx 9root root 4096Apr 1003:24pgsql

[postgres@rhel6 bin]$ ./pg_ctl -D /monchickey/pgsql_data/ -l logfilestartwaiting forservertostart.... doneserverstarted

7)停止数据库

./pg_ctl -D /monchickey/pgsql_data/ stop

3.2 使用rpm包安装

使用rpm包安装postgre数据库。需要使用4个rpm包。如果认为系统自带的postgre数据库安装包版本过低，从https://yum.postgresql.org网站上下载。本次安装使用rhel 6.5自带的安装包。

1)安装包信息及顺序

rpm-ivhpostgresql92-libs-9.2.4-1PGDG.rhel6.i686.rpmrpm-ivhpostgresql92-9.2.4-1PGDG.rhel6.i686.rpmrpm-ivhpostgresql92-server-9.2.4-1PGDG.rhel6.i686.rpmrpm-ivhpostgresql92-contrib-9.2.4-1PGDG.rhel6.i686.rpm

2)或者使用yum源进行安装

yum install-y postgresql-libsyum install-y postgresqlYum install-y postgresql-serverYum install-y postgresql-contrib

3)初始化数据库

[root@rhel6 Packages]# service postgresql initdbInitializing database: [ OK ]

4)启动数据库服务

[root@rhel6 Packages]# service postgresql startStarting postgresql service: [ OK ]

5)查看默认的psql

[postgres@rhel6 ~]$ psqlpsql (8.4.18)Type "help"forhelp.

6)停止数据库服务

[postgres@rhel6 ~]$ exitlogout[root@rhel6 Packages]# service postgresql stopStopping postgresql service: [ OK ]

7)卸载数据库

rpm-e postgresql-serverrpm-e postgresql-contribrpm-e postgresqlrpm-e postgresql-libs

3.3 编译安装

使用二进制文件进行编译方法，安装postgre数据库。

1)安装包信息及下载地址

Pg软件下载地址：http://ftp.postgresql.org/pub/source

Pg安装包名称 ：postgresql-11.0.tar.bz2

参考文档地址 ：https://www.postgresql.org/docs/10/installation.html

2)安装前系统检查,参照官方文档的要求，安装软件包

必须的安装包检查：

1：make --version检查

Eg:

[root@rhel6 ~]# make --version

GNU Make 3.81

Copyright (C) 2006 Free Software Foundation, Inc.

This is free software; see the source for copying conditions.

There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A

PARTICULAR PURPOSE.

2：gcc检查

[root@rhel6 ~]# rpm -aq gcc

gcc-4.4.7-4.el6.x86_64

可选软件包安装

1：Perl

2：Python

3：OpenSSL

......

3)解压安装包

tar -xjvf postgresql-11.0.tar.bz2 -C /usr/localEg：[root@rhel6 local]# cd postgresql-11.0/[root@rhel6 postgresql-11.0]# lltotal 744-rw-r--r-- 1 1107 1107 486 Oct 16 05:12 aclocal.m4drwxrwxrwx 2 1107 1107 4096 Oct 16 05:14 config-rwxr-xr-x 1 1107 1107 558874 Oct 16 05:12 configure-rw-r--r-- 1 1107 1107 83596 Oct 16 05:12 configure.indrwxrwxrwx 56 1107 1107 4096 Oct 16 05:14 contrib-rw-r--r-- 1 1107 1107 1192 Oct 16 05:12 COPYRIGHTdrwxrwxrwx 3 1107 1107 4096 Oct 16 05:14 doc-rw-r--r-- 1 1107 1107 3664 Oct 16 05:12 GNUmakefile.in-rw-r--r-- 1 1107 1107 284 Oct 16 05:12 HISTORY-rw-r--r-- 1 1107 1107 72717 Oct 16 05:15 INSTALL-rw-r--r-- 1 1107 1107 1682 Oct 16 05:12 Makefile-rw-r--r-- 1 1107 1107 1212 Oct 16 05:12 READMEdrwxrwxrwx 16 1107 1107 4096 Oct 16 05:15 src

4)编译

./configure

问题：

configure: error: readline library not found

If you have readline already installed, see config.log for details on the

failure. It is possible the compiler isn't looking in the proper directory.

Use --without-readline to disable readline support.

原因：

The GNU Readline library is used by default. It allows psql (the PostgreSQL command line SQL interpreter) to remember each command you type, and allows you to use arrow keys to recall and edit previous commands. This is very helpful and is strongly recommended. If you don't want to use it then you must specify the --without-readline option to configure. As an alternative, you can often use the BSD-licensed libedit library, originally developed on NetBSD. The libedit library is GNU Readline-compatible and is used if libreadline is not found, or if --with-libedit-preferred is used as an option to configure. If you are using a package-based Linux distribution, be aware that you need both the readline and readline-devel packages, if those are separate in your distribution.

解决方法：

1：./configure --without-readline

2：安装readline包

rpm -ivh redline

rpm -ivh redline-devel

5)编译并安装

make&& makeinstall

6)创建postgre用户及相应数据目录

UseraddpostgresSu- postgresmkdir-P /monkeys/pgsql11_data

7)数据库初始化：

/usr/local/pgsql/bin/initdb -D /monchickey/pgsql11_dataEg:[postgres@rhel6 bin]$ ./initdb -D /monchickey/pgsql11_dataThe files belonging to this database system will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized withlocale "en_US.UTF-8".The defaultdatabaseencodinghas accordingly been setto"UTF8".The defaulttextsearchconfiguration will be setto"english".

Datapage checksums aredisabled.

fixing permissions onexisting directory/monchickey/pgsql11_data ... okcreating subdirectories ... okselecting defaultmax_connections ... 100selecting defaultshared_buffers ... 128MBselecting dynamic sharedmemoryimplementation ... posixcreating configuration files ... okrunning bootstrap ... okperforming post-bootstrap initialization ... oksyncing datatodisk ... ok

WARNING: enabling "trust"authenticationforlocalconnectionsYou can changethis byediting pg_hba.conf orusingthe option-A, or--auth-local and --auth-host, the next time you run initdb.

Success. You can nowstartthe databaseserverusing:

./pg_ctl -D /monchickey/pgsql11_data -l logfilestart

8)启动postgre数据库

./pg_ctl -D /monchickey/pgsql11_data -l logfile startEg：[postgres@rhel6 bin]$ ./pg_ctl -D /monchickey/pgsql11_data -l logfilestartwaiting forservertostart.... doneserverstarted9)创建数据库

[postgres@rhel6 bin]$ ./createdb test[postgres@rhel6 bin]$ ./psqlpsql (11.0)Type "help"forhelp.postgres=# tTuples only is on.postgres=# lpostgres | 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/postgrestemplate1 |postgres | UTF8 |en_US.UTF-8| en_US.UTF-8 |=c/postgres +| || || postgres=CTc/postgrestest |postgres | UTF8 |en_US.UTF-8| en_US.UTF-8 |

第四章 Postgre数据库简单实用操作

在postgre数据库安装完成后，查看postgre数据库的后台进程，及端口信息来判断postgrep数据库的运行情况。进行简单操作，来尝试连接并使用数据库。

4.1 检查postgre数据库的进程及端口信息

1)查看postgre数据库进程

[postgres@rhel6bin]$ ps -ef | grep postroot1946 1 0 08:15 ? 00:00:00 /usr/libexec/postfix/masterpostfix1954 1946 0 08:15 ? 00:00:00 pickup -l -t fifo -upostfix1955 1946 0 08:15 ? 00:00:00 qmgr -l -t fifo -upostgres3914 1 0 09:39 pts/0 00:00:00 /monchickey/pgsql/bin/postgres -D /monchickey/pgsql_datapostgres3916 3914 0 09:39 ? 00:00:00 postgres: checkpointer processpostgres3917 3914 0 09:39 ? 00:00:00 postgres: writer processpostgres3918 3914 0 09:39 ? 00:00:00 postgres: wal writer processpostgres3919 3914 0 09:39 ? 00:00:00 postgres: autovacuum launcher processpostgres3920 3914 0 09:39 ? 00:00:00 postgres: stats collector processpostgres3921 3914 0 09:39 ? 00:00:00 postgres: bgworker: logical replication launcher

2)查看postgre端口，默认端口号是5432

[postgres@rhel6~]$ lsof -i:5432COMMANDPID USER FD TYPE DEVICE SIZE/OFF NODE NAMEpostgres3914 postgres 3u IPv6 74137 0t0 TCP localhost:postgres (LISTEN)postgres3914 postgres 4u IPv4 74138 0t0 TCP localhost:postgres (LISTEN)

3)查看psql的帮助信息

[postgres@rhel6 ~]$ psql --helppsql is the PostgreSQL interactive terminal.Usage:psql [OPTION]... [DBNAME [USERNAME]]

General options:-c, --command=COMMAND run only single command (SQL or internal) and exit-d, --dbname=DBNAME database name to connect to (default: "postgres")-f, --file=FILENAME execute commands from file, then exit-l, --list list available databases, then exit-v, --set=, --variable=NAME=VALUEsetpsql variableNAMEtoVALUE(e.g., -v ON_ERROR_STOP=1)-V, --version output version information, then exit-X, --no-psqlrc do not read startup file (~/.psqlrc)-1("one"), --single-transactionexecuteasa single transaction(ifnon-interactive)-?, --help[=options] show this help, then exit--help=commands list backslash commands, then exit--help=variables list special variables, then exit

Inputandoutputoptions:-a, --echo-all echo all input from-b, --echo-errors echo failed commands-e, --echo-queries echo commands sent to server-E, --echo-hidden display queries that internal commands generate-L, --log-file=FILENAME send session log to file-n, --no-readline disable enhanced command line editing (readline)-o, --output=FILENAME send query results to file (or |pipe)-q, --quiet run quietly (no messages, only query output)-s, --single-step single-step mode (confirm each query)-S, --single-line single-line mode (end of line terminates SQL command)

Outputformatoptions:-A, --no-align unaligned table output mode-F, --field-separator=STRINGfieldseparator forunaligned output(default: "|")-H, --html HTML table output mode-P, --pset=VAR[=ARG] set printing option VAR to ARG (see pset command)-R, --record-separator=STRINGrecordseparator forunaligned output(default: newline)-t, --tuples-only print rows only-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)-x, --expanded turn on expanded table output-z, --field-separator-zerosetfieldseparator forunaligned outputtozero byte-0, --record-separator-zerosetrecordseparator forunaligned outputtozero byte

Connectionoptions:-h, --host=HOSTNAME database server host or socket directory (default: "local socket")-p, --port=PORT database server port (default: "5432")-U, --username=USERNAME database user name (default: "postgres")-w, --no-password never prompt for password-W, --password force password prompt (should happen automatically)

Formore information, type"?"(forinternal commands) or"help"(forSQLcommands) fromwithinpsql, orconsult the psql sectioninthe PostgreSQLdocumentation.

Report bugs to.

5)psql中sql语法的帮助信息

[postgres@rhel6 bin]$ ./psqlpsql (11.0)Type "help"forhelp.postgres=# helpAvailable help:ABORT ALTER TEXT SEARCH TEMPLATE CREATE PUBLICATION DROP FUNCTION IMPORT FOREIGN......

6)查看当前数据库列表：

postgres-# lList of databasesName | Owner |Encoding | Collate |Ctype | Access privileges-----------+----------+----------+-------------+-------------+-----------------------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/postgrestemplate1 | postgres |UTF8 | en_US.UTF-8 |en_US.UTF-8| =c/postgres +|| || |postgres=CTc/postgres(3rows)

7)创建数据库

postgres=# CREATE DATABASE dbname WITH OWNER=postgres ENCODING='UTF-8';CREATE DATABASE

8)切换数据库

postgres=# c dbnameYou are now connected to database "dbname"as user "postgres".

9)查看数据库下所有表

dbname=# create table tab1 (id int,name char(20));CREATETABLEdbname=# dListofrelationsSchema| Name| Type| Owner--------+------+-------+----------public| tab1 | table| postgres

10)查看表信息

dbname-# d studentTable "public.student"Column | Type |Collation | Nullable |Default--------+---------------+-----------+----------+---------id | integer || notnull |name | character(32) || |number | character(5) || |Indexes:"student_pkey"PRIMARY KEY, btree (id)

原创：张红妮，云和恩墨服务部技术顾问；Oracle 运维DBA，对开源数据库也很有研究。