Installing and configuring 1C POSTGRESQL server. Install PostgreSQL

The crisis is unpleasant ... and many trying to save at least somehow, and install on Windows PostgreSQL Instead of MS SQL.

Although if your company is small speed, it is better to put MS SQL Server 2014 Express, because According to PostgreSQL, it works well on Linux, on Windows when settings by default PostgreSQL runs slower MS SQL Server 2014 Express.

Install PostgreSQL 9.2.4-1

Download the official package from the site 1C (users.v8.1c.ru) and launch the installation

We celebrate objects to the installation, if not marked

Install "as a service". To work this service will require a special user. Specify the account, domain (if available) and password.

If User. postgres.not started in the system, then the installer will ask the user? - Jmmem Yes

Let us turn to the next step and point to initialize the BD cluster, port 5432 and encoding UTF8.

If 1c server cluster and postgreSQLA different machines, then put a tick "maintain connections from any IP, and not just with localhost."

If the installation process is completed without problems, then you are lucky

There are problems, the solution is simple and elegant (tambourine can be left):

1. Create / Change Postgres account, enable it in the Administrators group

2. Exit the system and enter it under the postgres account

3. Run PostgreSQL installation

After installing 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, let's try to create new base 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.

In this article we will try to tell you how to independently publish a database on the server, how to tie Posgresql and 1C and which pitfalls can meet on your way.

Why it is necessary

Use allows you to:

1. Lower system requirements to user computers, due to the redistribution of the load;
2. Work with large volume databases;
3. Use slim client to work with information;
4. Optimize the execution time of query and appeals to the database;
5. Automate the execution of background and regulatory tasks;
6. Tune backup and speed up the database recovery time from the saved copy.

Conditions for solving the problem

At the start, we have:

• Personal Computer with 64 discharge operating windows system 7;
• The 1C installer, platform 8.3.10.2505;
• File Base data "Salary and personnel management", version 3.1.3.223;
• Optimized for 1C PostgreSQL Installer PostgreSQL 64-Bit 9.4.11;
• An additional utility for administering the PGAdmin server 4.

We begin to install.

Installing the server and setting it

Our task is not a question about the intricacies of the PostgreSQL server settings and any of its nuances. We will try as simple as possible and accessible to tell how to make friends with 1C. Based on the foregoing, we will not change the parameters that are automatically issued by the installer.

Reaching the window (Fig. 1) we will have to enter the password super user.

If you configure a working server, one of the tasks of which will be protected the data and the organization of third-party access to them, in addition to the password, you should also change the name of the main user of the server .

Tick \u200b\u200b"Maintain connection ..." Installed by default, in case the database server and 1C server are on the same computer, it can be removed.

Since only one 4GB tile is installed on the experimental computer. random access memoryThe program automatically can increase its volume, as reported by the window (Fig. 2).

Fig. 2.

In principle, there is nothing more to customize here. After installation, the corresponding folder appears in the main menu (Fig. 3).

Fig. 3.

From here you can stop, restart and start the server.

Its installation also does not represent any problems.

We perform its launch and see the window (Fig.4)

Fig.4

Further sequence of actions:

On this postgreSQL preparation, it seems to be completed, but what should I do if our server must serve several different databases? How to physically divide the places of storage?

To do this, you need to call context menu Tablespaces branches and create new element. For each database, you can register:

• Storage name;
• Location working directory;
• Create a comment containing detailed information about the location of the tables.

Now proceed to setting up 1C.

Installing and configuring 1C

Run the platform installer and install the following components:

1. Server 1C enterprise;
2. Server administration utility;
3. Server expansion modules;
4. Platform itself.

This is a mandatory set, the remaining components are installed at will (Fig. 9).

Fig.9.

In the second step, we will be offered to choose a user or create a new one (Fig. 10).

Fig.10.

In case we are going to use the current or other other than USR1CV8, the user, we must add to the following rights:

• Login as a service;
• Login as a batch task.

By running the administration utility, we are convinced that our server is active.

Add a new information base to the administration tree (Fig.11)

Fig.11

Here it is important to note that the creation of the 1C database on the PostGreSQL server can be performed from the application launch window. In this case:

A little more about this form:

1. Server cluster - If the base is on the same computer as the server, the LocalHost string will be used as a value;
2. The name of the base in the cluster - it is under this name that the server administrator will see the information base in the cluster tree;
3. DBMS type - as we raise the postgresql singer, it is necessary to specify it in the window;
4. The database name is to identify the database in the server's postgresql administration utility;
5. The user is a supersuber specified when creating a server;
6. Password - respectively Superwater password.

Thus, we created an empty 1c information database on the PostgreSQL server. To start working with it, it is enough in the "Configurator" mode, download the database (in DT format) uploaded from the file variant.

In order to work with our database to work with remote computerIn the fayer settings, open the corresponding ports.

In this instruction, we will tell (and show) how to set up a bundle 1C: Enterprise 8.3 and PostgreSQL 9.4.2from the moment of installation of both services, up to creation information base. About tuning this ligament can be found in.

The stages that we have to go through:

1. Installation 1C Servers: Enterprise (64-bit) for Windows
2. Installation PostgreSQL 9.4.2-1.1С
3. Creating an information database.

Read more under the cut!

Stage 0. Introductory data.

Server name - 1CServer.
Name account Servers - Administrator
Account Password - 123456AB

1c account name on server - Usr1cv8.
1C account password on server - 123456CD.

PostgreSQL account name on server - postgres.
Password PostgreSQL account on server - 123456EF.

Name of Superwater PostgreSQL - postgres.
Password Superwave PostgreSQL - 1234

Test Database Name - testdb.

Stage 1. Installation 1C Servers: Enterprise (64-bit) for Windows

Stage 2. Install PostgreSQL and PGAdmin.

Stage 3. Creating an information base 1C.

1. Before performing the following operations, disconnect the IPv6 on your network interface: Network Management Center and common access > Connecting to local network > Properties \u003e Remove the tick with Internet Protocol version 6 (TCP / IPv6).
2. We launch the client 1C: Enterprise and add a new database.
3. Creating a new information base\u003e Creating an information database without configuration (for example, you can have any configuration)\u003e On the server 1C: Enterprise\u003e
4. Fill all fields in accordance with our example (step 0):
   1C Server Cluster: Enterprise: 1CServer.
   The name of the information base in the cluster: testBd.
   Protected connection: Turned off
   Database type: PostgreSQL
   Database Server: 1CServer.
   Database name: testBd.
   Database user: postgres.
   User password: 1234
5. Next, further. Run the created base in the enterprise mode - everything works!

Once again we remind you that PostgreSQL can be dispersed well. Details B.

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. RAM RAM RAM resource allocation settings 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;

The question is what kind of DBMS - PostgreSQL or MS SQL for 1C is the most optimal, a lot of articles are devoted. In this article, we consider the steps to optimize both. Each VenDor DBMS has both its own recommendations for setup and the recommendations of the company 1C. It should be noted that depending on the equipment, configuration of servers and the number of users setting different loads, the details of the process of optimization of the DBMS under 1C and the implementation of the recommendations may vary.

Setting POSTGRESQL under 1C

Experience in operating database 1c on PostgreSQL has shown that the greatest performance and optimal operation 1C and PostgreSQL managed to achieve on Linux, so it is desirable to use it. But regardless of the operating system, it is important to remember that the default settings when installing PostgreSQL are intended only to start the DBMS server. About any industrial operation of speech can not go! The next step after starting will be the optimization of PostgreSQL under 1C:

• To begin with, we turn off ENERGY SAVING (otherwise it may be unpredictable to grow delayed responses from the database) and prohibit the swollen memory.
• Configure the basic parameters of the DBMS server (the configuration recommendations are described in sufficient detail, both on the official website of the vendor and 1C company, so we will dwell on the most important).
• In the model recommendations of 1C, it is proposed to disable Hyperthreading mechanisms. But testing Postgres-Pro on servers, with SMT enabled (Simultaneous Multi Threading), showed other results.

Set the Shared_Buffers parameter in RAM / 4 is a default recommendation, but the SQL Server example indicates that the more memory it is allocated, the better its performance (when the page reset is disabled to the paging file). That is, the more data pages are located in RAM, the less appeals to the disk. The question arises: why such a little cache? The answer is simple: if shared_buffers is large, then some of the unused pages are swapped to the disk. But how to track the moment when the reset stops, and the parameter indicator will be optimal? To achieve and access the optimal figure of Shared_Buffers, its value must be raised on the productivity daily (if possible) with a certain increment step and watch, at what point will the reset of the pages on the disk (Swap will increase).

In addition, the "big parameter" negatively affects the work with a multitude of small pages, which are the default size 8kb. Work with them increases overhead. What can be done with this to optimize 1c? In the POSTGRESQL 9.4 version, the Huge_Pages parameter appeared, which can be turned on, but only in Linux. By default, huge pages are included with the default size of 2048 Kb. Additional page data support must be included in the OS. Thus, optimizing the storage structure, you can exit a greater indicator of Shared_Buffers.

work_mem \u003d ram / 32..64 or 32MB..128MB Sets the amount of memory for each session to be used for internal sorting operations, association, etc., before temporary files are involved. If this volume is exceeded, the server will use temporary disk files, which can significantly reduce the speed of processing requests. This parameter is used when performing operators: Order by, Distinct, merge compounds, etc.

Calculate Additionally, this parameter can be as follows: (Shared_Buffers shared memory - Memory to other programs) / Number of active compounds. This value can be reduced by following the number of temporary files created. Such statistics on the size and number of temporary files can be obtained from the system representation PG_STAT_DATABASE.

effective_cache_size \u003d RAM - Shared_Buffers The main task of this parameter to prompt the request optimizer, which method of obtaining data to select: Full viewing or scanning by index. The higher the parameter value, the greater the likelihood of using the index scanning. At the same time, the server does not take into account that the data when executing the request can remain in memory, and the next query does not need to be raised from the disk.

Install PostgreSQL

Installing 1C on PostgreSQL under Windows is a fairly simple process. When starting the installation package, you must specify the UTF-8 encoding. In fact, this is the only interesting nuance and some other POSTGRESQL setting for 1C 8.3 from under Windows will not need. Installing and configuring PostgreSQL for 1C on Linux OS can cause a number of difficulties. To overcome them as an example, consider the launch of the work (using the distributions of the leading Russian vendor PostgreSQL-PRO and 1C) PostgreSQL on the Ubuntu 16.04 x64 server

Installation of 1C distributions for POSTGRESQL DBMS

3. Patch the specified POSTGRESQL DBMS distribution position:

2. Put PostgreSQL to the server;

3. POSTGRESQL DISBMS Installer can be commanded by the command:

tar -xvf postgresql-9.4.2-1.1c_amd64_deb.tar.bz2

4. Read the POSTGRESQL DBMS distribution to check the presence of the required locale in the system (by default ru_ru.utf-8):

5.If the system with which PostgreSQL will work was raised with a language different from Russian, you need to create new locals:

Locale-Gen RU_RU UPDATE-LOCALE LANG \u003d RU_RU.UTF8 DPKG-RECONFIGURE LOCALES

6. If the required locale still has, install it by default:

Locale -a Nano / etc / Default / Locale Replacing the contents on Lang \u003d RU_RU.UTF-8

7.After rebooting, set the necessary packages for our version PostgreSQL:

APT-GET INSTALL LIBXSLT1.1 SSL-CERT

8. The postgresql Package Package 9.4.2-1.1C is associated with the libicu version of LIBICU48. In repositories the desired version No longer, you can download it;

9. Patch and put in the directory where downloaded files are stored for PostgreSQL;

10. By pressing the directory with PostgreSQL files, we install the following commands in consistently:

CD<Путь к папке с файлами> dpkg -i libicu48_4.8.1.1-3ubuntu0.6_amd64.deb dpkg -i libpq5_9.4.2-1.1c_amd64.deb dpkg -i postgresql-client-common_154.1.1c_all.deb dpkg -i postgreSQL-COMMON_154.1C_all.deb dpkg -i PostgreSQL-Client-9.4_9.4.2-1.1c_amd64.deb dpkg -i postgresql-9.4_9.4.2-1.1c_amd64.deb dpkg -i PostgreSQL-CONTRIB-9.4_9.4.2-1.1c_amd64.deb

11. Ready. POSTGRESQL DBMS distribution set.

Installation of distributions PostgreSQL-PRO

To install the server, you must run the following commands in a row:

Sudo SH -C "ECHO" DEB HTTP: // 1c.postgrespro.ru/deb/ $ (LSB_RELEASE -CS) Main "\u003e /etc/apt/sources.list.d/postgrespro-1c.list" Wget --Quiet -O - \u200b\u200bhttp: // 1c.postgrespro.ru/keys/gpg-key-postgrespro-1c-92 | Sudo APT-KEY Add - && Sudo Apt-Get Update Sudo Apt-Get Install PostgreSQL-PRO-1C-9.4

To access the server, edit the parameters in the file pg_hba.conf.

cd.<Путь до каталога pg_hba.conf> cp pg_hba.conf pg_hba.conf.old bash -c "Echo" Local All Postgres TRUST "\u003e pg_hba.conf" bash -c "echo" host all ALL ALL MD5 "\u003e\u003e pg_hba.conf"

The file itself has the following structure:

The file is well documented, but on english language. Briefly consider the main parameters:

• Local local connection Only through UNIX
• Host. TCP / IP connection
• Hostssl. The encrypted SSL connection via TCP / IP (the server must be collected with SSL support, you also need to set the SSL parameter)
• Hostnossl. Easy TCP / IP Connection

• Trust.remote without authentication
• Reject. refuse without authentication
• Password. Password request in open text
• MD5mD5 password request
• LDAP. Check the name and password using the LDAP server
• Radius.check the name and password using the RADIUS server
• PAMcheck the name and password using the plug-in service

More detailed and detailed information can be viewed in the documentation for the POSTGRESQL product.

[Email Protected]: / Home / ASD # Service --Status-All | Grep Postgres [-] PostgreSQL [Email Protected]: / Home / ASD # Service PostgreSQL Start [Email Protected]: / Home / ASD # Service --Status-All | Grep Postgres [+] PostgreSQL

After completing the main installation, you need to configure configuration file. POSTGRESQL.CONF servers, according to the specifics of the PostgreSQL, Server 1C and the Ubuntu server configuration.

1c optimization under MS SQL Server

Install latest updates For SQL SEVER.

Operating system Reserves the place and scores it with zeros, which takes quite a long time at the following events:

• Database creation;
• Adding data files, transaction log, to an existing database;
• An increase in the size of an existing file (including autogro-operations);
• We restore databases or group files.

This problem is solved by adding a role (under which the server is running) to the Local Safety Policy "Performing Tomov Service Tasks".

If possible, you must disseminate the tempdb database (it is especially intensively used in mode controlled blocking RCSI) and transaction log on different disks.

On the server where the SQL server works, the power saving mode must be set to "High Performance".

In the folder with database files there should be no compression.

On the Memory tab for the server, you set the minimum bar in the amount of 50% of the total amount of memory. Maximum expect one of the formulas:

• Maximum memory \u003d total size - size over the OS - size under 1C (if it is, pre-measuring the meters used) or
• Maximum memory \u003d total volume - (1024 * Total / 16384).

We limit the DOP parameter "MAX DEGREE OF PARALLELISM" and put it in the value "1".

Actualizing statistics on schedule. Starting with SQL Server 2008, the statistics update causes reciprocating requests and, accordingly, clears the procedural cache, therefore it is not necessary to perform a separate procedure for cleaning the procedural cache.

Periodically carry out the table reindexing and defragmentation of indexes.

We establish the correct reservation policy. If you do not need to recover at the last moment of time to the collapse of the system, and the last minutes 5 or more for your business are not critical, then install the recovery model in the "simple". By this, you will speed up the speed when recording. The main thing is that Differentiated backup managed to run during the specified time.

We achieve improvement when working with TEMPDB when entering / output by creating additional data files. If logical processors are less than 8, it is recommended to create a data file for each logical processor. If logical processors are greater than 8, it is recommended to create 8 data files and, increasing to one with a multiplicity 4, be sure to estimate the load on TEMPDB.