Introduction to PostgreSQL - Installation and Initialization
PostgreSQL is a relational database, distributed under a BSD license and its source code freely available. It is the database engine open source and most powerful moment in its latest versions starts have not anything to envy to other commercial databases.
Its technical characteristics make it one of the databases more powerful and robust market. Its development began more than 15 years, and during this time, stability, performance, robustness, ease of administration and implementation of standard features that have been more taken into account during its development. In recent years have focused much on the speed of the process and demand characteristics in the business world.
The latest round of production is 8.2, the latest version is available at the time of this writing 8.2.4. PostgreSQL can be run in the vast majority of operating systems currently available, including Linux and Unix in all its variants (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) and Windows. The most important features and supported are:
* A database ACID 100%
* Keys outsiders (foreign keys)
* Joins
* Views (views)
* Triggers (triggers)
* Rules (Rules)
* Function / Stored (stored procedures) in many programming languages, including PL / pgsql (similar to PL / SQL oracle)
* Many kinds of information, opportunities for new types
* Supports the storage of binary large objects (pictures, videos, sound, ...)
* Inheritance tables (Inheritance)
* Pitre - point in time recovery
* Tablespaces
* Replication asincrona
* Nested transactions (savepoints)
* Two-phase commit
* Hot Backups (Online / hot backups)
* Unicode
* International character sets
* Multi-Version Concurrency Control (MVCC)
* Access via SSL encryption
* SQL92/SQL99
* APIs for programming in C / C + +, Java,. Net, Perl, Python, Ruby, Tcl, ODBC, PHP and many other languages.
* Complete documentation
Another feature to consider is how well it works with PostgreSQL large amounts of data and high competition, with many users simultaneously accessing the system. In the future will write an article about this.
Some of the physical limitations of PostgreSQL are:
Limit Value
-------------------------------------------------- ------------------
Maximum size database Unlimited (depends on your system
Storage)
Maximum size of 32 TB table
Maximum row size of 1.6 TB
Maximum field size of 1 GB
Maximum number of rows per table Unlimited
Maximum number of columns per table from 250 to 1600 (depending on type)
Maximum number of indexes per table Unlimited
-------------------------------------------------- ------------------
We could continue writing about many more features, but in this article have no place. You can go through the website of the project to deepen Postgresql.org item or pay attention to a series of articles on PostgreSQL that we will publish in the future.
A continución we describe how we can install and inicizalizar PosgreSQL.
Installation
PostgreSQL is available at any of the major Linux distributions. There RPM DEB og, distributed and that these distributions can be installed by default in the manner typical of each distribution.
If you want to install PostgreSQL in this way, go ahead, it is totally valid. I particularly, the database engine to use I like to compile and install it myself from source. In this way I have control over the version in use (especially important in production systems and databases). The rest of the article is based on this type of installation.
Requirements
We need a series of programs to be able to compile and install PostgreSQL (or any other program) from source. Most of what is needed is usually installed by default in most distributions if we install the packages related to development and compilers. " All these packages can be installed as standard, og deb rpm in the distribution of Linux that you use, if by chance you have not installed.
We need:
* GNU make (gmake)
* A compiler ISO / ANSI C. GCC compiler default Linux works perfectly.
* Tar, gzip or bzip2 to unpack the sources.
* GNU Readline Library
* Zlib compression library
* Perl and Python if you want to support PL / Perl and PL / Python
Compilation and installation
First we go down the sources of the version that we will install Postgresql.org. In our example the version 8.2.4 from the mirror (mirror) of Norway.
[user @ server] # cd / tmp /
[user @ server] # wget ftp://ftp.no.postgresql.org/pub/databases/postgresql/source/v8.2.4/postgresql-8.2.4.tar.bz2
- 21:17:45 - ftp://ftp.no.postgresql.org/pub/databases/postgresql/source/v8.2.4/postgresql-8.2.4.tar.bz2
=> Postgresql-8.2.4.tar.bz2 ` '
Resolving ftp.no.postgresql.org ... 158.36.2.10
Connecting to ftp.no.postgresql.org | 158.36.2.10 |: 21 ... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done. ==> PWD ... done.
==> TYPE I ... done. ==> CWD / pub/databases/postgresql/source/v8.2.4 ... done.
==> PASV ... done. ==> RETR postgresql-8.2.4.tar.bz2 ... done.
Length: 12,527,803 (12M) (unauthoritative)
100 %[=============================================== ============>] 12527803 692.34K / s ETA 00:00
21:18:02 (700.20 KB / s) - `postgresql-8.2.4.tar.bz2 'saved [12527803]
From now on we continue working as root.:
[user @ server] # su --
(or sudo-i if you're in Ubuntu)
Unpacked the sources:
[root @ server] # cd / tmp
[root @ server] # tar xjvf postgresql-8.2.4.tar.bz2
[root @ server] # cd postgresql-8.2.4 /
Now we have to configure and compile PostgreSQL. There are many parameters we can use to configure the software features available in the PostgreSQL we compile. You have a complete list of these parameters in 14.5.Installation Procedure section of the official documentation of PostgreSQL.
For my production servers, I set PostgreSQL ground with these parameters:
[root @ server] #. / configure - prefix = / usr / local - enable-nls - with-perl - with-python - with-openssl - with-pam - with-ldap
These parameters require that you have installed on your system perl, python, and openssl ldap (these packages can be installed as standard, og deb rpm in the distribution of Linux that you use).
To install at home will not necesiteis - with-pam - with-ldap, it's nice to have the rest to be able to connect to your database by encrypting network traffic, and be able to use PL / Perl and PL / Python to create functions. More information on PL / Perl - Perl Procedural Language and PL / Python - Python Procedural Language
Once the setup process is completed without error, we can begin to compile the sources:
[root @ server] # gmake
The compilation should finish without errors if you have installed all the packages you need depending on the settings you have defined. / Configure.
Once we have finished compiling the sources, we will install all the programs that make PostgreSQL:
[root @ server] # gmake install
If all went well, now we will have everything you need to use PostgreSQL (server, client, tools, libraries, etc.) available in our system. In our example will be installed around under / usr / local (as defined above with - prefix = / usr / local)
Initialisation
Now we have to initialize and configure our PostgreSQL installation before it can start creating OUR database.
The first thing we have to do is create a user on the system (postgres), which will be in our own system files generated by PostgreSQL, as well as in charge of running the database engine (PostgreSQL will refuse to start if try to do as root)
You can use your favorite program to create new users in your distribution, or run the following command:
[root @ server] # useradd - help
Usage: useradd ...
useradd - create a new user
-c comment Set the GECOS field for the new account
- show-defaults Print default values
- Save modified save-defaults default values
Use D-binddn dn "binddn" to bind to the LDAP directory
-d homedir Home directory for the new user
e-Date on which expires the new account will be disabled
-f inactive Days after a password expires until account is disabled
G-group ... List of supplementary groups
-g gid Name / number of the primary users group
k-skeldir Specify an alternative skel directory
-m Create home directory for the new user
-o Allow duplicate (non-unique) UID
-P path Search passwd, shadow and group file in "path"
-p password Encrypted password as returned by crypt (3)
uid-u Force the new userid to be the given number
-r, - system Create a system account
shell-s Name of the user's login shell
- service srv Add account to nameservice 'srv'
- help Give this help list
- usage Give a short usage message
-v, - version Print program version
Valid services for - service are: files, ldap
[root @ server] # useradd-m postgres
We created our postgres user without password. This means that the only way to become the root user is still using the command su - postgres.
Then we connect as the user postgres and initialize our cluster postgresql.
[root @ server] mkdir-p / var / pgsql / data
[root @ server] chown postgres / var / pgsql / data
[root @ server] # su - postgres
[postgres @ server] # / usr / local / bin / initdb-E utf8-U postgres-D / var / 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 en_US.UTF-8.
fixing permissions on existing directory / var / pgsql / data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers / max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in / var/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... 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-A option the
next time you run initdb.
Success. You can now start using the database server:
/ usr / local / bin / postgres-D / var / pgsql / data
or
/ usr / local / bin / pg_ctl-D / var / pgsql / data-l logfile start
We assume that we will have nuetras all databases and files related to postgresql in the directory / var / pgsql / data.
In subsequent articles, we will see how we can configure / organize our records better for maximum security and speed when using PostgreSQL in production systems. Also, we will see how we can configure PostgreSQL to make the most of this wonderful database.
At this time we can start our postgresql database and start using it without problems.
[postgres @ server] # / usr / local / bin / pg_ctl-D / var / pgsql / data-l / var / pgsql / data / postgresql.log start
starting server
If we stop PostgreSQL can use the following command:
[postgres @ server] # / usr / local / bin / pg_ctl-D / var / pgsql / data-m fast stop
waiting for server to shut down .... done
server stopped
We can start using the database with the customer potentisimo command line that is installed by default, your name / usr / local / bin / psql:
[postgres @ server] # / usr / local / bin / psql
Welcome to psql 8.2.4, 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
postgres = #
For a list of key commands that can be used in psql so you can start enjoying PostgreSQL, run the \?
postgres = # \?
Here are some examples of how to use this client:
postgres = # \ l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(3 rows)
postgres = # CREATE DATABASE test001;
CREATE DATABASE
postgres = # \ l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test001 | postgres | UTF8
(4 rows)
postgres = # \ c test001
You are now connected to database "test001".
test001 = # CREATE TABLE testing (
id INTEGER NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "testing_pkey" for table "testing"
CREATE TABLE
test001 = # \ d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
Public | testing | table | postgres
(1 row)
test001 = # \ q
[postgres @ server] #
That's it for this time