Shulou(Shulou.com)06/01 Report--

PostgreSQL is a very complex object-relational database management system (ORDBMS), and it is also the most powerful, rich and complex free software database system at present.

Os:centos6.5 x64

Ip:192.168.85.130

Hostname: vm2.lansgg.com

Pg version: postgresql-9.2.4.tar.bz2

1. Yum installation

II. Source code installation

III. System database

1. Yum installation

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

1.2. Initialize and start the database

[root@vm2 ~] # / etc/init.d/postgresql-9.2 initdb is initializing the database: [OK] [root@vm2 ~] # / etc/init.d/postgresql-9.2 start starts postgresql-9.2 service: [OK] [root@vm2 ~] # echo "PATH=/usr/ Pgsql-9.2/bin:$PATH "> > / etc/profile [root@vm2 ~] # echo" export PATH "> > / etc/profile

1.3, testing

[root@vm2 ~] # su-postgres-bash-4.1$ psqlpsql (9.2.19) enter "help" to get help. Postgres = #\ l Database list name | owner | character coding | proofreading rules | Ctype | access permissions-+- -+-- 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-line record) postgres=#

1.4. Change the administrator password

Modify the password of the PostgreSQL database user postgres (note that it is not a linux system account)

PostgreSQL database default will create a postgres database user as the database administrator, the default password is empty, we need to change to the specified password, here set to 'postgres'.

Postgres=# select * from pg_shadow Usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig-+-postgres | 10 | t | (1 line record) postgres=# ALTER USER postgres WITH PASSWORD 'postgres' ALTER ROLEpostgres=# select * from pg_shadow Usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig-+-- -+-postgres | 10 | t | md53175bce1d3201d16594cebf9d7eb3f9d | | (1 line record) postgres=#

1.5. Create a test database

Postgres=# create database testdb;CREATE DATABASEpostgres=#\ 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 TABLEtestdb=# insert into test values (1Magneto Lansgg`); INSERT 0 1testdb=# select * from test; id | name-+-1 | lansgg (1 line record)

1.7. View the table structure

Testdb=#\ d test; table "public.test" field | Type | modifier-+-id | integer | name | text |

1.8.Modification of PostgresSQL database configuration to achieve remote access

Modify the postgresql.conf file

If you want PostgreSQL to listen on the entire network, remove the # before listen_addresses and change listen_addresses = 'localhost' to listen_addresses =' *'

Modify client authentication profile pg_hba.conf

[root@vm2 ~] # vim / var/lib/pgsql/9.2/data/pg_hba.confhost all all 127.0.0.1 postgresql-9.2 32 identhost all md5 [root@vm2 ~] # / etc/init.d/postgresql-9.2 restart stop the postgresql-9.2 service: [OK] start postgresql-9.2 service: [OK] [root@vm2 ~] #

2. Source code installation

Stop the pgsql service installed by yum above and download the PostgreSQL source package

[root@vm2 ~] # / etc/init.d/postgresql-9.2 stop stop postgresql-9.2 service: [OK] [root@vm2 ~] # wget [root@vm2 ~] # tar jxvf postgresql-9.2.4.tar.bz2 [root@vm2 ~] # cd postgresql-9.2.4

View the INSTALL file

More INSTALL

The Short Version section of the INSTALL file explains how to install the PostgreSQL command, and the Requirements section describes the lib that the installation of PostgreSQL depends on. If it is long, try configure first. If error appears, then you need to check whether the requirements of Requirements are met.

Start compiling and installing the PostgreSQL database.

[root@vm2 postgresql-9.2.4] # / configure [root@vm2 postgresql-9.2.4] # gmake [root@vm2 postgresql-9.2.4] # gmake install [root@vm2 postgresql-9.2.4] # echo "PGHOME=/usr/local/pgsql" > > / etc/profile [root@vm2 postgresql-9.2.4] # echo "export PGHOME" > > / etc/profile [root@vm2 postgresql-9.2.4] # echo "PGDATA=/usr/local/pgsql/ Data "> / etc/profile [root@vm2 postgresql-9.2.4] # echo" export PGDATA "> > / etc/profile [root@vm2 postgresql-9.2.4] # echo" PATH=$PGHOME/bin:$PATH "> > / etc/profile [root@vm2 postgresql-9.2.4] # echo" export PATH "> > / etc/profile [root@vm2 postgresql-9.2.4] # source / etc/profile [root@vm2 postgresql-9.2.4] #

2.2. Initialize the database

Useradd-d / opt/postgres postgres # create if you don't have this account The previous yum installation has created [root@vm2 postgresql-9.2.4] # mkdir / usr/local/pgsql/data [root@vm2 postgresql-9.2.4] # chown postgres.postgres / usr/local/pgsql/data/ [root@vm2 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 "for us." Postgres ".This user must also own the server process.The database cluster will be initialized with locale" zh_CN.UTF-8 "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. Okcreating subdirectories... Okselecting default max_connections... 100selecting default shared_buffers... 32MBcreating configuration files... Okcreating template1 database in / usr/local/pgsql/data/base/1... Okinitializing pg_authid... Okinitializing dependencies... Okcreating system views... Okloading system objects' descriptions... Okcreating collations... Okcreating conversions... Okcreating dictionaries... Oksetting privileges on built-in objects... Okcreating information schema... Okloading PL/pgSQL server-side language... Okvacuuming database template1... Okcopying template1 to template0... Okcopying template1 to postgres... OkWARNING: enabling "trust" authentication for local connectionsYou 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/dataor / 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$ exitlogout [root@vm2 postgresql-9.2.4] # cp / root/postgresql-9.2.4/contrib/start-scripts/linux / etc/init.d/postgresql [root@vm2 postgresql-9.2.4] # chmod + x / etc/init.d/postgresql [root@vm2 postgresql-9.2.4] # / etc/init.d/postgresql startStarting PostgreSQL: ok [root@vm2 postgresql-9.2.4] # chkconfig-- add postgresql [root@vm2 postgresql -9.2.4] # chkconfig postgresql on [root@vm2 postgresql-9.2.4] #

1.4. Test use

[root@vm2 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$ psqlpsql (9.2.4) Type "help" for help.postgres=# create database testdb | CREATE DATABASEpostgres=#\ c testdb;You are now connected to database "testdb" as user "postgres" .testdb = # create table test (id int,name text,age int); CREATE TABLEtestdb=#\ d test Table "public.test" Column | Type | Modifiers-+-+-id | integer | name | text | age | integer | testdb=# insert into test values Id | name | age-+-+-1 | lansgg | 25 (1 row) testdb=#

3. System database

After you create a dataset cluster, the cluster contains three system databases, template1, template0, and postgres by default. Both template0 and postgres are copied from template1 during initialization.

Template1 and template0 databases are used to create databases. In PostgreSQL, a new database is created by copying from a template database. In the command to create a database, you can use the "- T" option to specify which database is used as a template to create a new database.

The template1 database is the default template for creating database commands, which means that the user database created by the command without the "- T" option is exactly the same as template1. Template1 can be modified, and if the template1 is modified, the results of these changes can also be reflected in the user database created after the modification. The existence of template1 allows users to make a custom template database, in which users can create some tables, data and indexes needed by the application. When they need to create databases with the same content many times in the future, they can use template1 as a template to generate.

Because the content of template1 may be modified by users, in order to meet the needs of users to create a "clean" database, PostgreSQL provides the template0 database as the initial backup data, and template0 can be used as a template to generate a "clean" database when needed.

The third initial database, postgres, is used to provide the initial user with a connectable database, just like a user's home directory in a Linux system.

All of the above system databases can be deleted, but the two template databases must change the datistemplate property of their tuples in pg_database to FALSE before deleting, otherwise it will prompt "cannot delete a template database".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.