Menu
Is free
check in
home  /  the Internet / 1C and Postgres installation on windows. Install PostgreSQL

1C and Postgres installation on windows. Install PostgreSQL

By default, PostgreSQL is configured to spend the minimum amount of resources to work with small databases up to 4 GB on not very productive servers. That is, if the case touches the systems more serious, then you will encounter large loss of database performance only because the default settings may not match the performance of your northern equipment. Resource allocation settings random access memory RAM to work postgreSQL stored in the file postgreSQL.conf..

Available from both the folder where PostgreSQL / DATA is installed and from PGAdmin:

In general, at the initial stage, in the event of difficulties and a slowdown in the work of the database, it is enough to increase three parameters for the eye of the users:

shared_buffers.

This is a memory size divided between PostgreSQL processes that are responsible for performing active operations. The maximum allowable value of this parameter is 25% of the total RAM

For example, at 1-2 GB RAM on the server, it is enough to specify in this parameter to 64-128 MB (8192-16384).

temp_Buffers.

This is the size of the buffer for temporary objects (temporary tables). The average value of 2-4% of the total RAM

For example, at 1-2 GB RAM on the server, it is enough to specify in this parameter a value of 32-64 MB.

work_mem.

This is a memory size used to sort and caching tables.

For the introduction of new values \u200b\u200binto force, the service will be restarted, so it is better to do in the outside of working time.

Two more important parameters are Maintenance_Work_mem (for Vacuum, Create Index and Other) and Max_Stack_Depth

Examples of optimal settings:

  • CPU: E3-1240 V3 @ 3.40GHZ
  • RAM: 32GB 1600MHz
  • Discs: Plextor M6Pro

postgreSQL.conf:

  • shared_Buffers \u003d 8GB.
  • work_mem \u003d 128mb.
  • maintenance_Work_mem \u003d 2GB.
  • fSYNC \u003d ON.
  • synchronous_Commit \u003d OFF.
  • wal_Sync_Method \u003d FDATASYNC.
  • checkpoint_segments \u003d 64.
  • sEQ_PAGE_COST \u003d 1.0.
  • rANDOM_PAGE_COST \u003d 6.0.
  • cPU_TUPLE_COST \u003d 0.01.
  • cPU_Index_Tuple_Cost \u003d 0.0005
  • cPU_PERATOR_COST \u003d 0.0025
  • effective_cache_size \u003d 24GB.

Useful queries:

BD blocking by users

Display all tables, more than 10 MB


from pg_tables
Where tablename not like 'sql_%' and pg_size_pretty (PG_TOTAL_RELATION_SIZE (CAST (TABLENAME AS TEXT))) LIKE '% MB%';

Definition of table sizes in the PostgreSQL database

SQL SELECT TABLENAME CODE, PG_SIZE_PRETTY (PG_TOTAL_RELATION_SIZE (CAST (TABLENAME AS TEXT))) AS SIZE
from pg_tables
WHERE TABLENAME NOT LIKE 'SQL_%'
Order by Size;

Users blocking a specific table

Code SQL SELECT A.USENAME, T.RELNAME, A.CURRENT_QUERY, MODE FROM PG_LOCKS L INNER JOIN PG_STAT_ACTIVITY A ON A.PROCPID \u003d L.PID INNER JOIN PG_STAT_ALL_TABLES T ON T.RELID \u003d L.RELATION WHERE T.RELNAME \u003d 'TABLENAME '; Code SQL Select Relation :: REGCLASS, MODE, A.USENAME, GRANTED, PID from pg_locks l inner join pg_stat_activity A ON A.Procpid \u003d L.Pid Where Not Mode \u003d 'AccessShareLock' and Relation Is Not Null;

Requests with exclusive locks

Code SQL SELECT A.USENAME, A.CURRENT_QUERY, MODE FROM PG_LOCKS L INNER JOIN PG_STAT_ACTIVITY A ON A.PROCPID \u003d L.PID WHERE MODE ILIKE '% EXCLUSIVE%';

Number of locks by users

Code SQL Select ausename, count (l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid \u003d l.pid WHERE NOT (MODE \u003d 'AccessShareLock') group by a.usename;

Number of connections by users

Code SQL Select Count (USENAME), USENAME FROM PG_STAT_ACTITIVITY GROUP by USENAME ORDER by Count (USENAME) DESC;

Once the size of the 1C file database: the enterprise of one of our customers has achieved in 32GB (yes, 32GB), as a result, everything is gradually started to slow down, and then it fell tightly, our clients asked us to solve this problem. SSD Enterprise class briefly sweetened to the pill, but after a while everything returned to the starting point. Well, here, here and to the grandmother do not go - go to the SQL version of the database.

Since we are " windows usersThe only two options for DBMS are available to us - this is MSSQL and PostgreSQL. The first is good to madness, but the cost did not please. And no longer pleased the news about additional 1c licenses to work with MSSQL. Therefore, PostgreSQL.

Detailed instructions from video available. In this article we will go through the key points.

Do not forget about backing up 1C databases!

Initial data:

  • OS. Windows Server 2008R2,
  • Intel Core I7-2600K 3.40GHz,
  • 32GB RAM,
  • Intel SSD DC3700 100GB (only under the database, OS on a separate SSD),
  • from 10 to 20 users in the database daily,
  • exchange with 5 nodes of distributed databases in the background.

Ominous, isn't it? Let's proceed.

1. Install PostgreSQL and PGAdmin.

No revelations on how to download PostgreSQL will not be our favorite site https://releases.1c.ru, section "Technological distributions". Download, put. Do not forget to install Microsoft Visual C ++ 2010 Runtime Libraries with Service Pack 1, which goes in an archive with a distribution. They fell on it: they did not install, tested a lot of pain.

Initialize a database cluster (check mark). And here you specify the account settings for PostgreSQL! IMPORTANT: you need to run the service "SECONDARY LOGON" (or on localized OS: "Secondary entrance to the system"). UTF8 encoding is also important!

pGAdmin in this assembly is becoming. We go to https://www.postgresql.org/ftp/pgadmin3/release/. At the time of writing the article fresh version 1.22.1. Swing it, put. Come in.

On the installation process "Administration of servers 1C enterprise" Let's not stop. This is a completely different topic. Yes, and there is nothing difficult there.

Create a SQL database in this snap, check in PGAdmin - the database will appear there if everything is correct.

2. Tuning PostgreSQL 9.4.2.

  • pg_hba.conf.
  • postgreSQL.conf.
  • pgpass.conf.

which lie here:

C: \\ Program Files \\ PostgreSQL \\ 9.4.2-1.1c \\ Data

If you are mistaken at least in one letter, after updating the PostgreSQL configuration, do not start. Find out what caused the reason will be difficult, even looking in windows logs. Therefore, do not change many parameters immediately and make backups.

To edit the config there comfortable toolAvailable directly from the main PGAdmin window. There he is:

What we change here:

  • shared_buffers. - Number of memory allocated by PGSQL for a joint page of pages. This memory is divided between all PGSQL processes. We divide the available RAM by 4. In our case, it is 8GB.
  • effective_cache_Size - Assembly Cache Size file System. It is believed: RAM - shared_buffers. In our case, it is 32GB - 8GB \u003d 24GB. But personally, I leave this parameter even lower, somewhere 20GB is still needed by RAM not only for PostgreSQL.
  • random_page_Cost. \u003d 1.5 - 2.0 for RAID, 1.1 - 1.3 for SSD. The cost of reading the random page (by default 4). The less SEEK Time disk system, the smaller (but\u003e 1.0) should be this parameter. Overly, the parameter increases the PGSQL leaning to the choice of plans to scan the entire table (PGSQL believes that it is cheaper to read the entire table cheaper than the random index). And that's bad.
  • temp_Buffers. \u003d 256MB. Maximum number of pages for temporary tables. That is, this is the upper limit of the size of the time tables in each session.
  • work_mem. - It is considered as: RAM / 32..64 - in our case, it turns out 1GB. Memory limit for processing one request. This memory is individual for each session. Theoretically, the maximum need for memory is equal to max_connections * work_mem, in practice it does not occur because most of the sessions are almost always hanging in anticipation.
  • bGWRITE_DELAY. - 20ms. Sleep time between recording cycles on disk background process Entries. This process He is responsible for synchronizing pages located in Shared_Buffers with a disc. Too much value of this parameter will result in an increase in the load on the checkpoint process and processes serving sessions (backend). A small value will take full load of one of the cores.
  • synchronous_commit. - OFF. DANGEROUSLY! Disable synchronization with disk at the time of the commit. Creates the risk of losing the latest multiple transactions (within 0.5-1 seconds), but guarantees the integrity of the database, there are guaranteed missions in the commit chain. But significantly increases productivity.

This is not everything that managed to learn from the Internet and articles on https://its.1c.ru. BUT! Even these settings are enough to speed up the job 1C: Enterprise at PostgreSQL.

In that concrete case After switching to PostgreSQL, users began to complain that 1C began to slow down even stronger than in the file version. But after that tuning the base "flew". Now everyone enjoy fast work. However, there are also its minuses in the form of locks. We do not plan to stop at this, we will dig further and upload useful changes in the postgreSQL configuration here.

PostgreSQL is a fairly modern and popular DBMS in the world. She did not go around 1C, choosing as one of the DBMS supported for the operation. Consider the installation instructions for PostgreSQL and its initial 8.3 under Windows.

PostgreSQL - free programThis is one of the decisive factors for choosing this DBMS.

To install the server, we will need two archives - enterprises (X86-64) and PostgreSQL distribution. In our example, platform version 8.3.4, and DBMS 9.1.2. They are better to take from official sources of 1C - disk or ITS website.

The first thing of the enterprise 8.3 (or 8.2). To do this, launch the setup.exe file from the archive. The installation is not much different from the usual installation of the client application, with the exception of some features:

1. Do not forget to select the desired items in the components:

2. Specify, the application will be launched from whose name. It is recommended to create a new user "usr1cv8". This user must have the right rights:

Get 267 video tutorials for 1C for free:

After installation, part 1c can be proceeded with the DBMS.

Install PostgreSQL

Run PostgreSQL-9.1.2-1.1C (x64) .msi file, in windows folder Select a subfolder 64 or 86, depending on the discharge of the OS. You can leave almost everything by default. It is necessary to pay attention to the following points:

1. Just like with 1C 8.3, the DBMS is installed as a service. You need to check the rights from the user used. The default system will create a new user, from which the name will be launched by:

2. Setting the 1C cluster. Here you must specify a password for the user:

To execute this item, the Secondary Logon Service (Secondary Logon) must be launched. If it is not running, it should be launched in the list of all services:

Creating a base of 1C per postgri

Creating a base is a very simple process. In the database list, you must click "Add", select the "Creating a new information base" item, specify the name of the database and select the location option - "on the 1C server: enterprises," where to fill the settings like the specified in the screenshot:

Where the password and username are those that you specified at the cluster setup phase.

That's all - you can work with the system.

Installation instructions PostgreSQL 9.0.3-3.1C on Windows Server 2008 x64

To install, use the Package - PostgreSQL 9.0.3-1C (x 86 or x 64).

Run MSI Package:

We celebrate the checkmark if the "Directory with Data", PSQL and PGAdmin III is noted. Further.

Install "as a service". To work this service will require a special user. Indicate account, Domain (if available) and password. Further.

If there is no such user in the system, then the master will suggest creating a new one. We answer "Yes" - the user is created. Further.

Now initialize the database. Specify port 5432. We check that the UTF 8 encoding is set by the POSTGRESQL user login and password (the system warns that the user password and the POSTGRESQL user password should not match - consider it). If 1c server cluster and PostgreSQL on different machines, then put a tick "maintain connections from any IP, and not just with localhost." Further.

An error may occur " SECONDARY LOGON ». Then we go to "Administration" - "Services". Start the "Secondary Login" or "Secondary Logon" service:


The program is set.

Through the "Start" menu - "All Programs" run the "PGAdmin III" administration utility.

Connect to the server. We enter the password for the user "Postgres". If you manage to connect, try to create a new base with the means of 1C itself.

Run the client part 1c. Click the "Add" button, set the 1C Server Server. Next, fill in the following: Database Server (IP or DNS The name of that server, wherein PostgreSQL) - if the same as the 1C cluster, then specify 127.0.0.1. Database Name: [Anyway]. User: "Postgres" Password: [Your_pall_Postgres]. Further.

We check that the base 1C is created successfully.

We will establish the assembly from Postgres Professional. On a 1C version page: Enterprise Find Installation Information on CentOS 7 Fresh Version PostgreSQL.

Connect the repository and install PostgreSQL 9.6:

Sudo RPM -IVH http://1c.postgrespro.ru/keys/postgrespro-1c-centos96.noarch.rpm sudo yum Makecache Sudo Yum Install PostgreSQL-PRO-1C-9.6

POSTGRESQL Basic Setup

Initialize service databases with Russian localization:

Su postgres /usr/pgsql-9.6/bin/initdb -d /var/lib/pgsql/9.6/data --Locale \u003d RU_RU.UTF-8 EXIT SERVICE POSTGRESQL-9.6 INITDB

Run PostgreSQL and add it to autoload:

SystemCTL Enable PostgreSQL-9.6 SystemCTL Start PostgreSQL-9.6 SystemCTL Status PostgreSql-9.6

We specify the password to the Postgres user, in order to be able to connect to the server remotely:

Su - Postgres PSQL ALTER USER POSTGRES WORPASSWORD; \\ Q EXIT.

Mcedit /var/lib/pgsql/9.6/data/pg_hba.conf.

in the file that opens, we unconsume and change the string:

hOST ALL ALL 127.0.0.1/32 Ident on the hOST ALL ALL 127.0.0.1/32 MD5

hOST ALL ALL 0.0.0.0/0 Ident on the hOST ALL 0.0.0.0/0 MD5

POSTGRESQL settings optimization (postgreSQL.conf) for 1C: Enterprise

Here will be settings for PostgreSQL working in virtual Machine ESXI 6.5.

Resources allocated for VM:

processor - 8 VCPU;

memory - 48 GB;

disk for OS - 50 GB on LUN hardware RAID1 from SAS HDD;

disk for database - 170 GB on SSD software RAID1

disc for Logs - 100 GB on Software RAID1 from SSD

To edit the settings, execute the command:

Mcedit /var/lib/pgsql/9.6/data/postgresql.conf.

Commented parameters that will need to be activated.

CPU

autovacuum_max_workers \u003d 4.

autovacuum_max_workers \u003d ncores / 4..2 but not less than 4

Number of autowacum processes. General rule - The more WRITE requests, the more processes. On the read-only database is enough of the same process.

sSL \u003d OFF.

Switching off encryption. For protected CDMS, encryption is meaningless, but leads to an increase in the CPU loading

Memory

shared_buffers \u003d 12GB.

shared_Buffers \u003d RAM / 4

Number of memory allocated PGSQL for a crested page. This memory is divided between all PGSQL processes. Operating system Itself caches the data, so there is no need to divert the entire cash operational memory for the cache.

temp_Buffers \u003d 256MB

Maximum number of pages for temporary tables. Those. This is the upper limit of the size of the time tables in each session.

work_mem \u003d 64mb.

work_mem \u003d ram / 32..64 or 32MB..128MB

Memory limit for processing one request. This memory is individual for each session. Theoretically, the maximum need for memory is equal to max_connections * work_mem, in practice it does not occur because most of the sessions are almost always hanging in anticipation. This recommendation value is used by an optimizer: it is trying to predict the size of the required memory for the request, and, if this value is greater than work_mem, then indicates the executor immediately create a temporary table. Work_mem is not in full sense limit: the optimizer can also miss, and the request will take more memory, perhaps. This value can be reduced by following the number of temporary files created:

maintenance_Work_mem \u003d 2GB.

maintenance_work_mem \u003d ram / 16..32 or work_mem * 4 or 256mb..4GB

Memory limit for serving tasks, such as the collection of statistics (Analyze), garbage assembly (Vacuum), create indexes and adding external keys. The size of the memory allocated under these operations should be comparable to physical size The largest index on the disk.

effective_cache_size \u003d 36gb.

effective_cache_Size \u003d Ram - Shared_Buffers

Assessment of the size of the cache file system. An increase in the parameter increases the system's tendency to choose IndexScan plans. And this is good.

Disks

effective_io_concurrency \u003d 5.

The estimated value of simultaneous requests to the disk system, which it can serve at the same time. For a single disk \u003d 1, for RAID - 2 or more.

random_page_Cost \u003d 1.3

random_page_Cost \u003d 1.5-2.0 for RAID, 1.1-1.3 for SSD

The cost of reading the random page (by default 4). The less SEEK Time disk system, the smaller (but\u003e 1.0) should be this parameter. Overly, the parameter increases the PGSQL leaning to the choice of plans to scan the entire table (PGSQL believes that it is cheaper to read the entire table cheaper than the random index). And that's bad.

autovacuum \u003d ON.

Turning on AvtoVakuuma.

autovacuum_naptime \u003d 20s.

Sleep time of the Avtovakuum process. Too much value will lead to the fact that the tables will not have time to evaporate and, as a result, the Bloat will grow and the size of the tables and indexes. Small value will lead to useless heating.

bGWRITER_DELAY \u003d 20MS.

Sleep time between recording cycles to the disc background process. This process is responsible for synchronizing pages located in Shared_Buffers with a disc. Too much value of this parameter will result in an increase in the load on the checkpoint process and processes serving sessions (backend). A small value will take full load of one of the cores.

bGWRITER_LRU_MULTIPLIER \u003d 4.0.

bGWRITER_LRU_MAXPAGES \u003d 400.

Parameters that control the intensity of the recording process record process. For one bgwriter cycle, it writes no more than it was written to a last loop multiplied by Bgwriter_LRU_Multiplier, but not more than BGWRITER_LRU_MAXPAGES.

synchronous_Commit \u003d OFF.

Disable synchronization with disk at the time of the commit. Creates the risk of losing the latest multiple transactions (within 0.5-1 seconds), but guarantees the integrity of the database, there are guaranteed missions in the commit chain. But significantly increases productivity.

wAL_KEEP_SEGMENTS \u003d 256.

wAL_KEEP_SEGMENTS \u003d 32..256.

The maximum number of Wal segments between checkpoint. Too frequent CheckPoint leads to a significant load on the disk subsystem by writing. Each segment has a size of 16MB

wAL_BUFFERS \u003d. 16 Mb.

The amount of shared memory, which will be used to buffer the WAL data, not yet recorded on the disk. The default value equal to -1, sets the size equal to 1/32 (about 3%) from, but not less than 64 Kb and no more than the size of one WAL segment (usually 16 MB). This value can be set manually if the selected automatically too little or large, but any positive number is less than 32 KB will be perceived as 32 KB. This parameter can be set only when the server is started.

The contents of WAL buffers are written to the disk when fixing each transaction, so that very large values \u200b\u200bare unlikely to bring significant benefits. However, the value of at least a few megabytes can increase the speed when recording on a loaded server, when a multiple clients immediately record transactions. Auto-tuning, valid for the default value (-1), in most cases chooses reasonable values.

default_statistics_target \u003d 1000.

Sets the target default statistics, spreading to the columns for which individual restrictions are not specified by the Alter Table Set Statistics command. The greater the set value, the longer it is required to perform Analyze, but the higher the quality of the scheduler's estimates can be. The value of this default parameter is 100.

checkpoint_completion_target \u003d 0.9.

The degree of "folding" checkpoint'a. The recording speed during checkpoint is regulated so that the checkpoint time was equal to the time passed from the past, multiplied by the checkpoint_completion_ target.

min_wal_size \u003d 4G.
max_wal_size \u003d 8g

min_wal_size \u003d 512MB .. 4G
Max_Wal_Size \u003d 2 * min_wal_size

The minimum and maximum amount of Wal files. Similar to checkpoint_segments

fSYNC \u003d ON.

Turning off the parameter leads to an increase in performance, but there is a significant risk of losing all data with sudden power off. ATTENTION: If the RAID has a cache and be in Write-Back mode, check the availability and functionality of the Kesha Raid controller battery! Otherwise, the data recorded in the RAID cache can be lost when the power is turned off, and, as a result, PGSQL does not guarantee data integrity.

row_Security \u003d OFF.

Disable recording level resolution control

enable_nestlooop \u003d OFF.

Includes or disabling the use of the plans to connect with nested cycles. It is impossible to completely exclude nested cycles, but when you turn off this parameter, the planner will not use this methodIf you can apply others. By default, this parameter is on.

Lock

max_locks_per_transaction \u003d 256.

Maximum number of index locks / tables in one transaction

Settings for 1C platform

standard_conforming_strings \u003d OFF.

Allow use symbol \\ for shielding

escape_string_warning \u003d OFF.

Do not issue a warning about the use of a symbol \\ for shielding

Security Setup

We will make it so that the PostgreSQL server is visible only for the server 1C: an enterprise installed on the same machine.

listen_addresses \u003d 'localhost'

If the server 1C: the company is installed on another machine or there is a need to connect to connect to the DBMS server using the PGAdmin snap, then instead localhost. you need to specify the address of this machine.

Storage database

PostgreSQL Like almost any DBMS is critical to the disk subsystem, therefore, to increase the speed of the DBMS, postgreSQL system, logs and databases themselves to different discs.

Start the server

SystemCTL Stop PostgreSQL-9.6

Transfer logs on 120GB SSD:

Mv /var/lib/pgsql/9.6/data/pg_xlog / raid120 mv /var/lib/pgsql/9.6/data/pg_clog / raid120 mv /var/lib/pgsql/9.6/data/pg_log / raid120

LN -S / RAID120 / PG_XLOG /VAR/LIB/PGSQL/9.6/data/pg_xlog LN -S / RAID120 / PG_CLOG /VAR/LIB/PGSQL/9.6/data/pg_clog LN -S / RAID120 / PG_LOG / VAR / LIB / PGSQL / 9.6 / DATA / PG_LOG

Also transfer the catalog with databases:

Mv /var/lib/pgsql/9.6/data/base / raid200

LN -S / RAID200 / BASE /VAR/LIB/PGSQL/9.6/data/Base

start the server and check its status

SystemCTL Start Postgresql-9.6 SystemCTL Status PostgreSql-9.6