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
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
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
before the ./configure command
Before building, you can specify the C ++ compiler
PostgeSQL can use the readline library, if you don’t have it and don’t want to install it, just specify the option
Hope everyone has Autotools ? Then forward to the assembly:
All gentlemen! Congratulations!
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
And then everything is clear
An important process. We must initialize the melon base cluster. We must do this on behalf of postgres
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:
If everything is correct, then add our script in init.d
Restarts the system to verify that our script is working.
We introduce
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
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)
Briefly tell about the basic utilities that are useful in the work.
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.
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:
- Complicated Queries
- Foreign keys
- Triggers
- Representation
- Transactional integrity
- Competitive Access Management with Multiple Versions
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
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.