PostgreSQL 9.2 Beginning!

I wanted to create the lovely encompassing manual Getting Start without any water, but including the basic bun for beginners according to the system PostgreSQL on Linux.

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, version 4.2, developed at the University of California at Berkeley computer science Department.

PostgreSQL is an open source descendant of original Berkeley code. It supports most of the SQL standard and offers many modern features:

the

the
• Complex queries
the
• Foreign keys
the
• Trigger
the
• View
the
• Transactional integrity (transactional integrity)
the
• Management of competitive access using versioning

In addition, PostgreSQL can be extended by the user in many ways, for example, by adding new
the

the
• data types
the
• of functions
the
• of operators
the
• aggregate functions
the
• index methods
the
• procedural languages

the

compilation and installation

Like all fans of mainstream PostgreSQL, we will of course collect, and download the finished package (in the Debian repositories, for example, is not the latest version). Here here is a lot of versions, download of course the best the last. At the time of writing this post is version 9.2.2

the

wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz
tar xzf postgresql-9.2.2.tar.gz

Now we have the directory with the source code of this database.
By default, the database files will be installed in /usr/local/pgsql, but this directory can be changed by setting

the

--prefix=/path/to/pgsql

the team ./configure
Before Assembly, you can specify the C++compiler

the

export CC=gcc

PostgeSQL can use readline library, if you have no desire to put it, just use

the

--without-readline

I hope everyone has Autotools? Then forward to the build:

the

cd postgresql-9.2.2
./configure --without-readline
sudo make install clean

All the gentlemen! Congratulations!

the

setup

We need to specify the data store of our databases (cluster) and run it.

There is one caveat — the owner of the data directory and the user that can run the database must not be root. It is made for the security of the system. Therefore, create a special user
the

sudo useradd postgres -p postgres -U-m

And further all is clear

the

sudo chown-R postgres:postgres /usr/local/pgsql

An important process. We need to initialize the database cluster melon. We need to do this on behalf of the user postgres

the

initdb -D /usr/local/pgsql/data

Now you need to add the PostgreSQL startup in autostart. For this there is already a finished script, and it is in postgresql-9.2.2/contrib/start-scripts/linux
This file can be open and pay attention to the following variables:
the

prefix is the place where we put PostgreSQL and was asked to ./configure

PGDATA is where the database cluster and where to have access our user postgres

PGUSER — this is the user on whose behalf everything will work

If everything is correct, then domvsem our script in the init.d

the

sudo cp ./postgresql-9.2.2/contrib/start-scripts/linux /etc/init.d/postgres
sudo update-rc.d postgres defaults

The system restarts to verify that our script is running.
Input

the

/usr/local/pgsql/bin/psql -U postgres

And if you are working with the database, then the configuration was successful! Congratulations!

Now it is important to talk about methods of authorization.
In /usr/local/pgsql/data/pg_hba.conf just has the necessary setup.

the

# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust

The first line is responsible for the local connection, the second connection about IPv4, and the third is IPv6.
The last parameter is just the same authorization method. It and consider (some basic)
the

trust — access to the database can get anyone under any name with the connection.

reject — reject unconditionally! It is suitable for filtering certain IP addresses

password — requires a password. Not suitable for local users, only users created by the command CREATE USER

ident — allows only the user logged in the file /usr/local/pgsql/data/pg_ident.conf to establish a connection with the database.

Will briefly describe the main tools which will be useful in work.

the

tools for working with the database

pg_config

Returns information about the currently installed version of PostgreSQL.

initdb

Initialiserer new data store (the database cluster). A cluster is a collection of databases managed by one instance of the North. initdb has to be run on behalf of the future owner of the server (as specified above on behalf of postgres).

pg_ctl

Controls the process of the PostgreSQL server. Allows you to start, restart, stop the server, specify the log file and more.

psql

Client to work with the base of the melon. Allows to perform SQL operations.

createdb

Creates a new database. By default, the database is created on behalf of the user who runs the command. However, to set the other — you must use the-O option (if the user has the required privileges for this). In fact — this is a wrapper SQL CREATE DATABASE command.

dropdb

Removes the database. Is a wrapper around the SQL command DROP DATABASE.

createuser

Adds a new user to a database. Is a wrapper around the SQL command CREATE ROLE.

dropuser

Removes a database user. Is a wrapper around the SQL command DROP ROLE.

createlang

Adds a new programming language in PostgreSQL database. Is a wrapper around the SQL command CREATE LANGUAGE.

droplang

Removes a programming language. Is a wrapper around the SQL command DROP LANGUAGE.

pg_dump

Creates a backup (dump) of the database to a file.

pg_restore

Restores a backup (dump) of the database from a file.

pg_dumpall

Creates a backup (dump) the whole cluster to the file.

reindexdb

Produces a reindexing of the database. Is a wrapper around the SQL command REINDEX.

clusterdb

Produces perekristallizatsii tables in the database. Is a wrapper around the SQL command CLUSTER.

vacuumdb

the garbage Collector and the optimizer of the database. Is a wrapper around the SQL command VACUUM.

the

Managers to work with the database

As for the Manager working with the database, then have php Manager phpPgAdmin and GUI the Manager pgAdmin. I should note that they are both bad support latest version of PostgreSQL.

P. S If you forgot something, tell me — adds.

Article based on information from habrahabr.ru