PostgreSQL Service Deployment

PostgreSQL is a very complex object-relational database management system (ORDBMS), and is currently the most powerful, feature richest and most complex free software database system.
os:centos6.5 x64

ip:192.168.85.130

Hostname:vm2.lansgg.com

PG Version: POSTGRESQL-9.2.4.TAR.BZ2

One, yum installation
Second, the source code installation

Third, the system database

1. Yum Installation

[[email protected] ~]# wget 
[[email protected] ~]# rpm -vhi pgdg-redhat92-9.2-8.noarch.rpm
[[email protected] ~]# yum install postgresql92-server postgresql92-contrib -y

1.2. Initialize and start the database

[[email protected] ~] # /etc/init.d/postgresql-9.2 initdb
Initializing database: [OK]
[[email protected] ~] # /etc/init.d/postgresql-9.2 start
Start the postgresql-9.2 service: [OK]
[[email protected] ~] # echo "PATH = / usr / pgsql-9.2 / bin: $ PATH" >> / etc / profile
[[email protected] ~] # echo "export PATH" >> / etc / profile

1.3. Testing

[[email protected] ~] # su-postgres
-bash-4.1 $ psql
psql (9.2.19)
Enter "help" for help.

postgres = # \ l
                                      Library list
    Name | Owner | Character Encoding | Proofing Rules | Ctype | Access
----------- + ---------- + ---------- + ------------- +- ----------- + -----------------------
  postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
  template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | = c / postgres +
            | | | | | postgres = CTc / postgres
  template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | = c / postgres +
            | | | | | postgres = CTc / postgres
(3 lines of records)

postgres = #

1.4. Modify the Administrator password

Modify the password for the PostgreSQL database user Postgres (note that it is not a Linux system account)
The PostgreSQL database is created by default to create a Postgres database user as the administrator of the database, the default password is empty, we need to change to the specified password, which is set to ' Postgres '.

postgres = # select * from pg_shadow;
  usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil |
---------- + ---------- + ------------- + ---------- + --- -------- + --------- + -------- + ---------- + -----------
  postgres | 10 | t | t | t | t | | |
(1 line record)

postgres = # ALTER USER postgres WITH PASSWORD ‘postgres’;
ALTER ROLE
postgres = # select * from pg_shadow;
  usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil |
---------- + ---------- + ------------- + ---------- + --- -------- + --------- + ------------------------------- ------ + ---------- + -----------
  postgres | 10 | t | t | t | t | md53175bce1d3201d16594cebf9d7eb3f9d | |
(1 line record)

postgres = #

1.5. Create a test database

postgres = # create database testdb;
CREATE DATABASE
postgres = # \ c testdb;
You are now connected to the database "testdb", user "postgres".
testdb = #

1.6. Create a test table

testdb = # create table test (id integer, name text);
CREATE TABLE
testdb = # insert into test values (1, ‘lansgg’);
INSERT 0 1
testdb = # select * from test;
  id | name
---- + --------
   1 | lansgg
(1 line record)

1.7. View table structure

testdb = # \ d test;
   Datasheet "public.test"
  Field | Type | Modifier
------ + --------- + --------
  id | integer |
  name | text |

1.8. Modify the Postgressql database configuration for remote access

Modify the Postgresql.conf file

If you want PostgreSQL to listen to the entire network, remove the # before listen_addresses and change listen_addresses = ' localhost ' to listen_addresses = ' * '
To modify the client authentication configuration file pg_hba.conf

[[email protected] ~] # vim /var/lib/pgsql/9.2/data/pg_hba.conf
host all all 127.0.0.1/32 ident
host all all all md5
[[email protected] ~] # /etc/init.d/postgresql-9.2 restart
Stop the postgresql-9.2 service: [OK]
Start the postgresql-9.2 service: [OK]
[[email protected] ~] #

2. Source code Installation

Stop the above Yum installed Pgsql service and download the PostgreSQL source package

[[email protected] ~] # /etc/init.d/postgresql-9.2 stop
Stop the postgresql-9.2 service: [OK]
[[email protected] ~] # wget
[[email protected] ~] # tar jxvf postgresql-9.2.4.tar.bz2
[[email protected] ~] # cd postgresql-9.2.4

View the Install File
More INSTALL
The short Version section of the install file explains how to install PostgreSQL commands, Requirements section describes the lib that is dependent on the installation of PostgreSQL, long, first configure try, if error occurs, Then you need to check whether the requirements requirements are met.
Start compiling and installing the PostgreSQL database.

[[email protected] postgresql-9.2.4]# ./configure
[[email protected] postgresql-9.2.4]# gmake
[[email protected] postgresql-9.2.4]# gmake install
[[email protected] postgresql-9.2.4]# echo "PGHOME=/usr/local/pgsql" >> /etc/profile
[[email protected] postgresql-9.2.4]# echo "export PGHOME" >> /etc/profile
[[email protected] postgresql-9.2.4]# echo "PGDATA=/usr/local/pgsql/data" >> /etc/profile
[[email protected] postgresql-9.2.4]# echo "export PGDATA" >> /etc/profile
[[email protected] postgresql-9.2.4]# echo "PATH=$PGHOME/bin:$PATH" >> /etc/profile
[[email protected] postgresql-9.2.4]# echo "export PATH" >> /etc/profile
[[email protected] postgresql-9.2.4]# source /etc/profile
[[email protected] postgresql-9.2.4]#

2.2. Initialize the database

useradd -d / opt / postgres postgres ###### If you do not have this account, create it, it was already created for us when Yum was installed
[[email protected] postgresql-9.2.4] # mkdir / usr / local / pgsql / data
[[email protected] postgresql-9.2.4] # chown postgres.postgres / usr / local / pgsql / data /
[[email protected] postgresql-9.2.4] # su-postgres

-bash-4.1 $ / usr / local / pgsql / bin / initdb -D / usr / local / pgsql / data /
The 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 with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

fixing permissions on existing directory / usr / local / pgsql / data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in / usr / local / pgsql / data / base / 1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects ’descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL / pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    / usr / local / pgsql / bin / postgres -D / usr / local / pgsql / data
or
    / usr / local / pgsql / bin / pg_ctl -D / usr / local / pgsql / data -l logfile start

-bash-4.1 $

1.3. Add to System service

-bash-4.1$ exit
logout
[[email protected] postgresql-9.2.4]#  cp /root/postgresql-9.2.4/contrib/start-scripts/linux /etc/init.d/postgresql
[[email protected] postgresql-9.2.4]# chmod +x /etc/init.d/postgresql
[[email protected] postgresql-9.2.4]# /etc/init.d/postgresql start
Starting PostgreSQL: ok
[[email protected] postgresql-9.2.4]# chkconfig --add postgresql
[[email protected] postgresql-9.2.4]# chkconfig postgresql on
[[email protected] postgresql-9.2.4]#

1.4. Test use

[[email protected] postgresql-9.2.4]# su - postgres
-bash-4.1$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

-bash-4.1$ psql
psql (9.2.4)
Type "help" for help.

postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=# create table test(id int,name text,age int);
CREATE TABLE
testdb=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 name   | text    | 
 age    | integer | 

testdb=# insert into test values(1,‘lansgg‘,25);
INSERT 0 1
testdb=# select * from test;
 id |  name  | age 
----+--------+-----
  1 | lansgg |  25
(1 row)

testdb=#

3. System Database

After the dataset cluster is created, the cluster contains three system databases Template1, TEMPLATE0, and postgres by default. Both Template0 and Postgres are copied from the TEMPLATE1 during the initialization process.

The template1 and TEMPLATE0 databases are used to create databases. PostgreSQL creates a new database in the same way that it is copied from the template database, and the "-t" option is used in the command to create the database to specify which database is to be created as a template to create a new database.

The TEMPLATE1 database is the default template for creating database commands, which means that user databases created with commands without the "-t" option are identical to template1. Template1 can be modified, and if Template1 is modified, the results of these modifications can be reflected in the user database created after the modification. The presence of template1 allows users to make a custom template database where users can create tables, data, indexes, etc. that are needed by the application, and can be generated using template1 as a template when they need to create a database of the same content multiple times in the future.

Since the content of template1 is likely to be modified by the user, PostgreSQL provides the TEMPLATE0 database as the initial backup data in order to meet the needs of the user to create a "clean" database, and when needed, can use template0 as a template to generate "clean" 's database.

The third initial database, Postgres, is used to provide the initial user with a database that can be connected, just like a user's home directory in a Linux system.

The above system database can be deleted, but the two template database must be deleted before deleting the Datistemplate property of the tuple in pg_database to false, otherwise the deletion will prompt "cannot delete a template database"

This article is from the "Big Wind" blog, please be sure to keep this source http://lansgg.blog.51cto.com/5675165/1888614

PostgreSQL Service Deployment