Geekly Articles each Day
⬆️ ⬇️

PostgreSQL 9.2 Start!

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



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



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 extended by the user in many ways, for example, by adding new





Assembly and installation



Like all PostgreSQL mainstream fans, we will of course build and not download ready-made packages (for example, there is no latest version in Debian repositories). Here there are a lot of versions, of course the best is to download the latest. 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 source directory of this excellent 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 command ./configure

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 build: 



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


All gentlemen! Congratulations!



Customization



We need to specify the data storage of our databases (cluster) and run 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 cluster of melon bases. We need to do this on behalf of the postgres user. 



 initdb -D /usr/local/pgsql/data


Now you need to add the PostgreSQL startup to autostart. To do this, there is a ready-made script and it is in postgresql-9.2.2 / contrib / start-scripts / linux

This file can be opened and pay attention to the following variables:

  • prefix is where we put PostgreSQL and set it in ./configure
  • PGDATA is where the database cluster is stored and where our postgres user should have access
  • PGUSER is the user from whom 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


Restart the system to check that our script is working.

We enter 



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


And if the database operation window appears, the setup was successful! Congratulations!

By default, a database is created named postgres



Now it is important to talk about authorization methods.

In /usr/local/pgsql/data/pg_hba.conf there is just the setting required 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 responsible for the local connection, the second is for the IPv4 connection, and the third is for the IPv6 protocol.

The most recent parameter is the authorization method. His and consider (only basic)

  • trust - anyone can access the database under any name, having a connection with it.
  • reject - reject unconditionally! It is suitable for filtering certain IP addresses.
  • password - requires mandatory password entry. Not suitable for local users, only users created with the CREATE USER command
  • ident — allows only a user registered in the /usr/local/pgsql/data/pg_ident.conf file to establish a connection with the database.


Briefly talk about the basic tools that will be useful in the work.



Utilities for working with the base



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 should be run on behalf of the future owner of the server (as mentioned above on behalf of postgres).



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



psql
Client to work with the base melon. 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, you must use the -O option (if the user has the necessary privileges for this). In essence, this is a SQL wrapper for the CREATE DATABASE command.

dropdb
Removes a database. Is a wrapper to the SQL command DROP DATABASE.



createuser
Adds a new user base melon. It is a SQL wrapper for the CREATE ROLE command.

dropuser
Removes a database user. Is a wrapper SQL command DROP ROLE.



createlang
Adds a new programming language to the PostgreSQL database. It is a SQL wrapper for the CREATE LANGUAGE command.

droplang
Removes a programming language. Is a wrapper SQL command DROP LANGUAGE.



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

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

pg_dumpall
Creates a backup (dump) of the entire cluster to a file.



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



clusterdb
Reclusters the tables in the database. CLUSTER SQL wrapper.



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



Base Managers



As for the database manager, the php manager is the phpPgAdmin and GUI manager pgAdmin . I should note that they both do not support the latest version of PostgreSQL very well.



PS If you forgot something, tell me - I will add.

Source: https://habr.com/ru/post/168601/

More articles:



All Articles