Linux install PostgreSQL and start and close tutorials

Operating system version


[Root@web103 ~]# More/etc/issue
CentOS release 5.9 (Final)
Kernel \ r \m
[Root@web103 ~]# Uname-a
Linux web103 2.6.18-348.el5 #1 SMP Tue 8 17:53:53 EST 2013 x86_64 x86_64 x86_64 gnu/linux

download corresponding PostgreSQL corresponding RPM package
Because the operating system version is CentOS 5.9 of 64-bit Linux, so download the corresponding version of the PRM package, mainly download server,client,contrib,libs four packages


[root@web103 ~]# mkdir PG
[root@web103 ~]# CD PG
[root@web103 pg]# wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-9.4.4-1PGDG.rhel5.x86_64.rpm
--2015-06-16 20:44:52--http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-9.4.4-1PGDG.rhel5.x86_64.rpm
Resolving yum.postgresql.org ... 174.143.35.196, 2001:4800:1501:1::196
Connecting to yum.postgresql.org|174.143.35.196|:80 ... Connected.
HTTP request sent, awaiting response ... OK
length:1807607 (1.7M) [Application/x-redhat-package-manager]
Saving to: ' postgresql94-9.4.4-1pgdg.rhel5.x86_64.rpm '
%[============================================================================================================= ======&GT] 1,807,607 73.6k/s in 30s
-06-16 20:45:24 (58.1 kb/s)-' postgresql94-9.4.4-1pgdg.rhel5.x86_64.rpm ' saved [1807607/1807607]

[root@web103 pg]# wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-server-9.4.4-1PGDG.rhel5.x86_64.rpm
--2015-06-16 20:45:35--http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/ postgresql94-server-9.4.4-1pgdg.rhel5.x86_64.rpm
Resolving yum.postgresql.org ... 174.143.35.196, 2001:4800:1501:1::196
Connecting to yum.postgresql.org|174.143.35.196|:80 ... Connected.
HTTP request sent, awaiting response ... OK
length:6175991 (5.9M) [Application/x-redhat-package-manager]
Saving to: ' postgresql94-server-9.4.4-1pgdg.rhel5.x86_64.rpm '
%[============================================================================================================= ======&GT] 6,175,991 58.5k/s in 2m 4s
-06-16 20:47:42 (48.6 kb/s)-' postgresql94-server-9.4.4-1pgdg.rhel5.x86_64.rpm ' saved [6175991/6175991]

[root@web103 pg]# wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/ postgresql94-contrib-9.4.4-1pgdg.rhel5.x86_64.rpm
--2015-06-16 20:47:51--http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/ postgresql94-contrib-9.4.4-1pgdg.rhel5.x86_64.rpm
Resolving yum.postgresql.org ... 174.143.35.196, 2001:4800:1501:1::196
Connecting to yum.postgresql.org|174.143.35.196|:80 ... Connected.
HTTP request sent, awaiting response ... OK
length:664051 (648K) [Application/x-redhat-package-manager]
Saving to: ' postgresql94-contrib-9.4.4-1pgdg.rhel5.x86_64.rpm '
%[============================================================================================================= ======&GT] 664,051 28.1k/s in 53s
-06-16 20:48:46 (12.3 kb/s)-' postgresql94-contrib-9.4.4-1pgdg.rhel5.x86_64.rpm ' saved [664051/664051]

[root@web103 pg]# wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-libs-9.4.4-1PGDG.rhel5.x86_64.rpm
--2015-06-16 20:51:10--http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-libs-9.4.4-1PGDG.rhel5.x86_64.rpm
Resolving yum.postgresql.org ... 174.143.35.196, 2001:4800:1501:1::196
Connecting to yum.postgresql.org|174.143.35.196|:80 ... Connected.
HTTP request sent, awaiting response ... OK
length:233206 (228K) [Application/x-redhat-package-manager]
Saving to: ' postgresql94-libs-9.4.4-1pgdg.rhel5.x86_64.rpm '
%[============================================================================================================= ======&GT] 233,206 70.3k/s in 3.2s
-06-16 20:51:16 (70.3 kb/s)-' postgresql94-libs-9.4.4-1pgdg.rhel5.x86_64.rpm ' saved [233206/233206]

[root@web103 pg]# ls
postgresql94-9.4.4-1pgdg.rhel5.x86_64.rpm postgresql94-libs-9.4.4-1pgdg.rhel5.x86_64.rpm
postgresql94-contrib-9.4.4-1pgdg.rhel5.x86_64.rpm postgresql94-server-9.4.4-1pgdg.rhel5.x86_64.rpm

Install PostgreSQL RPM package


[root@web103 pg]# RPM-IVH *.rpm
Warning:postgresql94-9.4.4-1pgdg.rhel5.x86_64.rpm:header V3 DSA Signature:nokey, key ID 442df0f8
Preparing ... ########################################### [100%]
:p ostgresql94-libs ########################################### [25%]
:p ostgresql94 ########################################### [50%]
:p ostgresql94-contrib ########################################### [75%]
:p ostgresql94-server ########################################### [100%]

Create PostgreSQL Default library


[root@web103 pg]# service postgresql-9.4 Initdb
Initializing database: [OK]
[Root@web103 data]# pwd
/var/lib/pgsql/9.4/data
[Root@web103 data]# ls-ltr
Total 120
-RW-------1 postgres postgres 21265 June 20:52 postgresql.conf
-RW-------1 postgres postgres June 20:52 postgresql.auto.conf
DRWX------3 Postgres postgres 4096 June 20:52 Pg_xlog
-RW-------1 postgres postgres 4 June 20:52 Pg_version
DRWX------2 postgres postgres 4096 June 20:52 Pg_twophase
DRWX------2 postgres postgres 4096 June 20:52 PG_TBLSPC
DRWX------2 postgres postgres 4096 June 20:52 Pg_subtrans
DRWX------2 postgres postgres 4096 June 20:52 Pg_snapshots
DRWX------2 postgres postgres 4096 June 20:52 pg_serial
DRWX------2 postgres postgres 4096 June 20:52 Pg_replslot
DRWX------4 Postgres postgres 4096 June 20:52 Pg_multixact
DRWX------4 Postgres postgres 4096 June 20:52 pg_logical
-RW-------1 postgres postgres 1636 June 20:52 pg_ident.conf
-RW-------1 postgres postgres 4224 June 20:52 pg_hba.conf
DRWX------2 postgres postgres 4096 June 20:52 Pg_dynshmem
DRWX------2 postgres postgres 4096 June 20:52 Pg_clog
DRWX------5 Postgres postgres 4096 June 20:52 Base
DRWX------2 postgres postgres 4096 June 21:16 Pg_log
DRWX------2 postgres postgres 4096 June 21:16 Global
-RW-------1 postgres postgres June 21:39 Postmaster.pid
-RW-------1 postgres postgres June 21:39 postmaster.opts
DRWX------2 postgres postgres 4096 June 21:39 Pg_stat
DRWX------2 postgres postgres 4096 June 21:39 pg_notify
DRWX------2 postgres postgres 4096 June 22:00 pg_stat_tmp

In addition, you can create the following two different ways


Initdb-d/var/lib/pgsql/9.4/data
Pg_ctl-d/var/lib/pgsql/9.4/data

set PostgreSQL to boot automatically


[root@web103 pg]# chkconfig postgresql-9.4 on
[root@web103 pg]# chkconfig--list|grep Post
postgresql-9.4 0:off 1:off 2:on 3:on 4:on 5:on 6:off

View default Create PostgreSQL user


[root@web103 data]# More/etc/passwd|grep Post
Postgres:x:26:26:postgresql Server:/var/lib/pgsql:/bin/bash

Add PATH environment variable


-bash-3.2$echo "path= $PATH:/usr/pgsql-9.4/bin;export PATH" >>~/.bash_profile

start the PostgreSQL database


--Method 1
-bash-3.2$ postgres-d/var/lib/pgsql/9.4/data
----OR
-bash-3.2$ postgres-d/var/lib/pgsql/9.4/data >~/pg.log 2>&1 &

Method 2
Pg_ctl start-l ~/pg.log-d/var/lib/pgsql/9.4/data

Here if Pgdata is configured in the environment variable, then-D can also not specify,-l for the specified log directory, recommend the use of Encapsulated Method 2 start PG

View PostgreSQL Process information


[root@web103 data]# ps-ef|grep post|grep-v grep
Postgres 4432 1 0 21:39? 00:00:00/usr/pgsql-9.4/bin/postgres-d/var/lib/pgsql/9.4/data
Postgres 4433 4432 0 21:39? 00:00:00 Postgres:logger Process
Postgres 4435 4432 0 21:39? 00:00:00 Postgres:checkpointer Process
Postgres 4436 4432 0 21:39? 00:00:00 Postgres:writer Process
Postgres 4437 4432 0 21:39? 00:00:00 Postgres:wal Writer Process
Postgres 4438 4432 0 21:39? 00:00:00 postgres:autovacuum Launcher Process
Postgres 4439 4432 0 21:39? 00:00:00 postgres:stats Collector Process

Here you can see that PG is similar to Oracle, there are log processes, checkpoint processes, write processes, etc. (Specific later analysis)

Stop PostgreSQL Database


-bash-3.2$ Pg_ctl stop-d/var/lib/pgsql/9.4/data
Waiting for server to shut down ... done
Server stopped
-bash-3.2$ Ps-ef|grep post|grep-v grep
Root 6036 499 0 22:07 pts/0 00:00:00 su-postgres
Postgres 6037 6036 0 22:07 pts/0 00:00:00-bash
Postgres 6113 6037 0 22:08 pts/0 00:00:00 ps-ef

PostgreSQL Default Listening port

[root@web103 pgsql]# Netstat-natp|grep Postgres
TCP 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 4432/postgres




Linux PostgreSQL installation problem to solve the whole process

PostgreSQL's Official download address is:

Ftp://ftp.postgresql.org/pub/v7.1.3/postgresql-7.1.3.tar.gz
http://www.postgresql.org/

If you download the latest development version, you will need to download and install Flex (version number greater than 2.5.4) and Bison (version number greater than 1.28)

Designers must establish corresponding users and groups for security reasons, PostgreSQL cannot run as root.

# Useradd Postgre (automatically set up Postgre group)
{Note: After adding a user with the Useradd command, this does not enter the password, the system will not be empty password continue, but no matter what password can not use this account, we must use root through passwd username to set a password to activate this user before you can use; The second is when Useradd, through the-p parameter, that is, useradd [username]-p [password] can directly assign the password and activate the user. }

The installation process is not complicated and is similar to other source version installation methods:
Extract to/usr/local/src:
# tar Xvfz postgresql-7.1.3.tar.gz
# CD postgresql-7.1.3
#./configure--prefix=/usr/local/pgsql
# make
# make Install
# Chown-r Postgre.postgre/usr/local/pgsql

This is not the end of the installation, there are some finishing work to do:
# VI ~postgre/.bash_profile
Add to:

Pglib=/usr/local/pgsql/lib
Pgdata= $HOME/data
Path= $PATH:/usr/local/pgsql/bin
Manpath= $MANPATH:/usr/local/pgsql/man
Ld_librarypath= $LD _librarypath:/usr/local/pgsql/lib
Export Pglib pgdata PATH Manpath Ld_librarypath

Modify Configuration
One.
$vi/home/postgre/data/pg_hba.conf
At the bottom of the file:
# "Local" to Unix domain socket connections only
Local All
# IPV4 Local connections:
Host all All 192.168.18.0/32 (* Set the gateway here) Trust
# IPV6 Local connections:
Host All:: 1/128
Two.
$vi/home/postgre/data/postgresql.conf
#-Connection Settings-
# listen_addresses = ' localhost ' # What IP address (es) to listen on;
Delete the # before this sentence and assign it as ' * '
To read: listen_addresses= ' * '


After the above configuration, to Postgres user login,
# Su-postgre
To create a database directory:
$ mkdir Data

To start the database engine:

$ initdb
[Postgre@www postgre]$ Initdb
This database system'll is initialized with username "Postgre".
This user would own all the data files and must also own the server process.

Fixing permissions on pre-existing data directory/home/postgre/data
Creating database System Directory/home/postgre/data/base
Creating Database XLOG Directory/home/postgre/data/pg_xlog
Creating Template Database In/home/postgre/data/base/template1
Creating Global Relations In/home/postgre/data/base
Adding Template1 database to Pg_database

Creating View Pg_user.
Creating View Pg_rules.
Creating View Pg_views.
Creating View Pg_tables.
Creating View pg_indexes.
Loading pg_description.
Vacuuming database.

Success. The can now start the database server using:

/usr/local/pgsql/bin/postmaster-d/home/postgre/data
Or
/usr/local/pgsql/bin/pg_ctl-d/home/postgre/data Start

Start the Postgre database service in a later way
$ postmaster-i-D ~/data &
[1] 22603
[Postgre@www postgre]$ Debug:data Base System is starting up at Thu 31 02:00:44 2002
Debug:data Base System is shut down at Thu 31 01:57:58 2002
Debug:data Base System is in production to Thu 31 02:00:44 2002

This allows PostgreSQL to use the database located in/usr/local/pgsql/data, allowing Internet users to connect (-i) and run in the background.

Create a database
The first example of how you can access a database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, we use a single database for each project and each user.

Your node administrator may have created a database for you to use. He should have told you the name of the database. If so you can omit this step and skip to the next section.

To create a new database, called MyDB in our example, you can use the following command:

$ createdb MyDB It should generate the following response:
{Note: If Bash:createdb:command not found appears, you may not be able to pgsql
Bin is added to the PATH environment variable, which is executed under the shell as
$export path= $PATH:/usr/local/pgsql/bin can be the same, if the order does not save
You can also use this method at the time to try and see if the bin directory is added to path.
}

CREATE database If so, this step is successful and you can ignore the rest of the section.

If you see information like the following

Createdb:command not found so that is PostgreSQL not installed well. Either it's not installed at all, or your search path is not set correctly. Try calling the command with an absolute path try:

$/usr/local/pgsql/bin/createdb MyDB This path may not be the same on your node. Contact your administrator or check the installation instructions to get the correct location.

Another response might be this:

Createdb:could not connect to database Postgres:could don't connect to server:
No such file or directory
is the server running locally and accepting
Connections on Unix domain socket "/tmp/.s.pgsql.5432"? This means that the server did not start, or did not start where createdb expected. Also, you should check the installation instructions or find an administrator.

Another response might be this:

Createdb:could not connect to the database Postgres:FATAL:user "Joe" does not
exist here mentions your own login name. These phenomena can occur if the administrator does not create PostgreSQL user accounts for you. (The PostgreSQL user account and operating system user account are different.) If you are an administrator, see Chapter 18 for help creating a user account. You need to become an operating system user that installs PostgreSQL (usually Postgres) to create the first user account. It may also be that your PostgreSQL username differs from your operating system username; In this case, you need to use the-u switch or use the PGUSER environment variable to declare your PostgreSQL username.

If you have a database user account, but do not have the required permissions to create the database, you will see the following things:

Createdb:database creation Failed:ERROR:permission denied to create database not all users have been authorized to create new databases. If PostgreSQL refuses to create a database for you, then you need to have the node administrator give you permission to create the database. Consult your node administrator when this occurs. If you install PostgreSQL yourself, then you should log in as the user you started the database server and refer to the manual to complete the assignment. [1]

You can also create a database with a different name. PostgreSQL allows you to create any number of databases on a single node. The database name must start with a letter and is less than 63 characters in length. A convenient approach is to create a database with the same name as your current username. Many tools assume that the database name is the default database name, so you can save your keystrokes. To create such a database, you only need to type

$ createdb
If you don't want to use your database anymore, then you can delete it. For example, if you are the owner (creator) of the database mydb, you can delete it with the following command:

$ dropdb mydb (for this command, the database name is not the default user name.) So you have to declare it. This action physically deletes all files associated with the database and cannot be canceled, so be sure to think before doing this.

Notes
[1] Why do you do that? Explanation: The PostgreSQL username is separated from the operating system user account. If you are connected to a database, you can choose which PostgreSQL user name to join, and if you do not choose, then the default is your current operating system account. If so, there is always a PostgreSQL user account with the same name as the operating system user that is used to start the server, and usually the user has permission to create the database. If you do not want to log in as the user, you can also declare a-u option anywhere to select a joined PostgreSQL username.

Accessing the database
Once you have created the database, you can access it:



A terminal program that runs PostgreSQL interaction, called Psql, allows you to interactively enter, edit, and execute SQL commands.

Use our existing graphical front-end tools, such as pgaccess or an office suite with ODBC support, to create and manage databases. This method is not covered in this tutorial.

Write a client application that uses one of several language bindings. These possibilities are discussed in more depth in part IV.

You may need to start psql and experiment with the examples in this tutorial. You can activate it for the MYDB database using the following command:

$ psql MyDB If you omit the database name, it defaults to your user account name. You already know the outline in the previous section.

In Psql, you will see the following welcome message:

Welcome to Psql 8.1, the PostgreSQL Interactive terminal.

Type: \copyright for distribution Terms
\h for help with SQL commands
\? For help with Psql commands
\g or terminate with semicolon to execute query
\q to quit

The last line of mydb=> may also be

Mydb= #这个提示符意味着你是数据库超级用户 is most likely to occur when you install PostgreSQL yourself. Being a superuser means you are not restricted by access control. For the purposes of this tutorial, it is not important that you have superuser.

If you run into a problem when you start Psql, go back to the previous section. The method of diagnosing createdb is very similar to the method of diagnosing Psql, if the former can run then the latter should also be able to run.

The last line Psql prints is the prompt, which means that psql is listening to you and you can type SQL queries into a psql-maintained workspace. Test the following command:

Mydb=> SELECT version ();
Version
----------------------------------------------------------------
PostgreSQL 8.1 on I586-pc-linux-gnu, compiled by GCC 2.96
(1 row)
Mydb=> SELECT current_date;
Date
------------
2002-08-31
(1 row)

Mydb=> SELECT 2 + 2;
? column?
----------
4
(1 row)
The PSQL program has some internal commands that are not part of the SQL command. They start with a backslash, "\". Some of these commands are listed in the Welcome message. For example, you can use the following command to get the help syntax for various PostgreSQL SQL commands:

Mydb=> \h
To exit Psql, type

Mydb=> \q then Psql quits and returns you to the command line shell. (To get more information about internal commands, you can type \ at the psql prompt.) The full functionality of Psql is documented in part VI. If the PostgreSQL is installed correctly, you can also read the document by typing man psql on the operating system's shell prompt. In this document, we will not use these features explicitly, but you can use them at the right time.