Hello.

Write this article and maybe even a series of articles devoted to the Postgres, I forced the hunger for information about the Postgres database, especially in conjunction with 1C.

What struck me as odd? Much that is described here is everywhere! And like nuances.

Nuance 1. Installation.

I use Linux CentOS 5.x, although processes must not fundamentally different from other vendors.

Patching Process will not describe by 1C, are everywhere (eg http://habrahabr.ru/blogs/linux/107321/ )

I use the standard on most .rpm 1C.

Before installing, you must understand how the server will be used. When it comes to the basis of the central office, where the information from the other units will flock and put all sorts of analytics, it is better that the server was isolated only by the DBMS.

It is important to know that Postgres is not able to specify different encodings for individual databases! Therefore, be sure to do the following:

#su - postgres

#initdb --locale = uk_UA.UTF8 --encoding = UTF8 / path / to_your / data /

This commando initsializurete you store in UTF8 encoding for the locale uk_UA - at 1C is Ukrainian (Ukraine).

Next, be sure to check the number of shared memory, ie, shared memory:

#cat / proc / sys / kernel / shmall

#cat / proc / sys / kernel / shmmax

You get to the screen on the number of shared memory in bytes. Dividing by 1024 dvazhy you get the size in MB. This is the size of shared memory between PostgreSQL processes that need to carry out active operations.

Parameter shared_buffers, in /path/to_your/data/postgresql.conf file must be min 1/8 - max 1/4 RAM.

If you received value exceeds the standard value shmall and shmmax, they should be increased, such as:

#echo 68719476736> / proc / sys / kernel / shmmax

#echo 4294967296> / proc / sys / kernel / shmmax

All. Now all dolozhno start and work.

Nuance 2. Catalogues Postgres

Never remove the Postgres files by hand, do so only by regular utilities, or if you know that it can be done.

Important folders:

$ PG_HOME / data - everything is stored here

$ PG_HOME / data / base - our database

$ PG_HOME / data / pg_xlog - transaction logs

$ PG_HOME / data / pg_clog - commit logs

Directory size pg_xlog depends paramterov:

checkpoint_segments and checkpoint_timeout

checkpoint_segments - determines the maximum number of segments (each 16 MB) of the log transactions between checkpoints, checkpoint_timeout - specifies the number of seconds between checkpoints, the work setting, which comes first.

So consider (checkpoint_segments * 2 + 1) * 16 MB, make sure that you have enough free space for pg_xlog. Remember, the more write operations in your database, the more you need to put checkpoint_segments, whereas in the case of collapse, Postgres need less recovery time.

If you have trouble, you have recovered and transaction logs you do not need, look toward pg_resetxlog.

Nuance 3. Connecting 1C

I hope everyone who wants to use 1C + Postgres understand architecture trehzvenki or MVC?

If not, remember. MVC - is a model view controller.

Controller - This is our 1C, with the pledged configuration that describes a particular business logic.

View - this is our client 1C, which displays everything that gives us the server 1C (8.2 totlko way possible to completely separate the business logic from the client)

Model - it is our database where 1C stores all your data

So, prescribing base 1C cluster specify exactly the server where your database is located.

Working with the model, one cluster 1C uses one user to connect to the database. Therefore track-level database, a "scoundrel" has launched a particular query to a database, you can not :( But vseravno have utility pg_top useful!

So, what we need to specify in /path/to_your/data/postgresql.conf:

listen_addresses = '*' # listening to connections on all network interfaces, server

max_connections = 200 # how many users can connect to the database

And nezabyt about /path/to_your/data/pg_hba.conf:

host all all 127.0.0.1/32 trust

pg_hba.conf responsible for Security Connections, in this example, we said that may only be connected to the localhost.

If you did everything correctly, 1C connect without problems.

I am waiting for your comments.

If Postgres is interesting to you, I can prepare articles on the themes:

Tuning, Backup, Evacuation, replication, cluster, Statistics, Partitioning and others.