February 12, 2013 at 12:04

PostgreSQL 9.2 Getting Started!

  • Tutorial
I wanted to create a wonderful comprehensive Getting Start manual without any water, but including the basic PostgreSQL Linux buns for beginners.

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

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


In addition, PostgreSQL can be expanded by the user in many ways, for example, by adding new
  • data types
  • functions
  • operators
  • aggregate functions
  • method index
  • procedural languages


Assembly and installation


Like all fans of the PostgreSQL mainstream, we will of course collect and not download ready-made packages (in the Debian repositories, for example, there is no latest version). Here are many versions, of course, the latest is the best download. At the time of writing this post is version 9.2.2

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 a directory with the source code for this beautiful database.
By default, the database files will be installed in the / usr / local / pgsql directory, but this directory can be changed by specifying

--prefix=/path/to/pgsql

before the ./configure command
Before building, you can specify the C ++ compiler

export CC=gcc

PostgeSQL can use the readline library, if you don’t have it and don’t want to install it, just specify the option

--without-readline

Hope everyone has Autotools ? Then forward to the assembly:

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

All gentlemen! Congratulations!

Customization


We need to specify the data warehouse of our databases (cluster) and start it.

There is one caveat - the owner of the data directory and the user who can run the database should not be root. This is done for system security. Therefore, we will create a special user
sudo useradd postgres -p postgres -U -m

And then everything is clear

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

An important process. We must initialize the melon base cluster. We must do this on behalf of postgres

initdb -D /usr/local/pgsql/data

Now you need to add PostgreSQL launch to autostart. There is a ready-made script for this and it lies in postgresql-9.2.2 / contrib / start-scripts / linux
This file can be opened and the following variables can be noted:
  • prefix is where we put PostgreSQL and asked in ./configure
  • PGDATA is where the database cluster is stored and where our postgres user should have access
  • PGUSER is the very user on whose behalf everything will work

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

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

Restarts the system to verify that our script is working.
We introduce

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

And if the window for working with the database appears, then the setup was successful! Congratulations!
By default, a database called postgres is created.

Now it’s important to talk about authorization methods.
In /usr/local/pgsql/data/pg_hba.conf there are just the necessary settings for this

# 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 for the local connection, the second is for the IPv4 connection, and the third is for IPv6.
The very last parameter is just the same authorization method. We will consider it (only basic)
  • trust - anyone can access the database under any name that has a connection to it.
  • reject - reject unconditionally! This is suitable for filtering specific IP addresses.
  • password - requires a password. Not suitable for local users, only users created by CREATE USER
  • ident - allows only a user registered in the /usr/local/pgsql/data/pg_ident.conf file to establish a connection with the database.

Briefly tell about the basic utilities that are useful in the work.

Utilities for working with the database


pg_config
Returns information about the currently installed version of PostgreSQL.

initdb
Initializes a new data warehouse (database cluster). A cluster is a collection of databases managed by a single instance of the north. initdb must be run on behalf of the future server owner (as indicated above on behalf of postgres).

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

psql
Client for working with melon database. Allows you 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 specify another one, you must use the -O option (if the user has the necessary privileges for this). Essentially, this is a SQL wrapper for the CREATE DATABASE command.
dropdb
Deletes a database. It is a wrapper of the SQL DROP DATABASE command.

createuser
Adds a new melon database user. It is a wrapper of the SQL CREATE ROLE command.
dropuser
Deletes a database user. It is a wrapper of the SQL DROP ROLE command.

createlang
Adds a new programming language to the PostgreSQL database. It is a wrapper of the SQL CREATE LANGUAGE command.
droplang
Removes a programming language. It is a wrapper of the SQL DROP LANGUAGE command.

pg_dump
Creates a backup (dump) of the database to a file.
pg_restore
Restores the backup (dump) of the database from a file.
pg_dumpall
Creates a backup (dump) of the entire cluster to a file.

reindexdb
Re-indexes the database. It is a wrapper for the SQL REINDEX command.

clusterdb
Reclusters the tables in the database. It is a wrapper of the SQL CLUSTER command.

vacuumdb
Garbage collector and database optimizer. It is a wrapper for the SQL command VACUUM.

Base Managers


As for the database manager, that is, the php manager is phpPgAdmin and the GUI manager is pgAdmin . I must say that they both do not support the latest version of PostgreSQL poorly.

PS If you forgot something, say - I will add.
Tags:

Also popular now: