Developers Club geek daily blog

Compalex: comparison of schemes of two databases

2 years, 1 month ago
Let's assume, you have prod and test of database. At some point the developer has made changes to test base, but has forgotten to make these changes to fighting base. If it is often used table, the situation very quickly becomes obvious as in logs there will be errors in SQL queries and the chief with reproaches "what @#$%» starts calling you.

But sometimes changes affect seldom used tables, or changes at first sight are not absolutely obvious (for example, someone has changed the field length of VARCHAR and at you lines began to be cut off, or someone has added index because of which requests on test base are executed much quicker).

Still option — you have carried out software updating and everything has ceased to work for you. The heap of unclear errors from scratch, application lies, users are not happy.

In such cases happens it is very useful to look than bases differ and to draw the corresponding conclusions.


Read more »


PostgreSQL 9.5: any news? Part 2. TABLESAMPLE

2 years, 1 month ago
We continue the overview of innovations in PostgreSQL 9.5. First part here.
From the author
I apologize for delay with release of the second part. Initially I was going to let out the second part of article in week after the first, but, in connection with big employment, could not make it. Therefore I have decided that I will publish not big articles, and in the small portions, but is more often.

Read more »


Installation and setup of generation of tayl on the basis of OSM of data under Windows

2 years, 3 months ago

Disclaimer


Recently on work the problem of generation of tayl has evolved from OSM of data. Has looked, has read articles, but * nix-systems appeared everywhere, and I had available server under control of Windows. Finally, more or less distinct tutoriat was, but it slightly obsolete and not everywhere is detailed, therefore it was necessary to tinker. After successful end has thought that my experience will be useful.

Whom has interested, I ask under kat.

Read more »


PostgreSQL 9.5: any news? Part 1. INSERT … ON CONFLICT DO NOTHING/UPDATE and ROW LEVEL SECURITY

2 years, 3 months ago
In the 4th quarter 2015 PostgreSQL 9.5 release is expected. As always, the new version except new bugs brings new features and "buns". In this article two of them, namely INSERT … by ON CONFLICT DO NOTHING/UPDATE and Row-level security will be considered. Already there was the second alpha version therefore the most impatient can set and try it new functionality.
It is possible to download here

Read more »


PostgreSQL: Receptions on the prodakshena

2 years, 3 months ago
It is possible to read many books on databases, to write heap of applications on autsors or for itself. But thus it is necessary to step on rake, during the work with really big bases / tables especially when downtime on the big project wants to be minimized, and even better absolutely to avoid. Here the simplest operations, such as change of structure of the table can become more complex challenge. The most interesting cases, problems, rake and their solutions from personal experience with which we on the Pushwoosh project had to face are described under cat. In article there are no beautiful pictures, but there is a lot of dry text.

image

Read more »


PostgreSQL 9.3 + Pgpool-II

2 years, 4 months ago
Has decided to share with you experience of setup of cluster of PostgreSQL 9.3 consisting of two notes in which management pgpool-II, without use of Stream Replication (WAL) is engaged. I hope, it will be interesting to somebody.

Scheme:

image

As you understand, it will be two separate servers which will be managed by pgpool-II.

Configuration of notes with PostgreSQL:
Both servers are identical on the hardware components.
  • 4vCPU;
  • 16 Memory Gb;
  • CentOS 6.5;

Disks:
  • 50 Gb? system;
  • 100 Gb? pg_xlog
  • 500 Gb? the directory with data


Example,
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        48G  7.4G   38G  17% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/sda1       194M   28M  157M  15% /boot
/dev/sdb1        99G  4.9G   89G   6% /var/lib/pgsql/9.3/data/pg_xlog
/dev/sdc1       493G  234G  234G  50% /var/lib/pgsql/9.3/my_data


Configuration of notes with pgpool-II:
  • 4vCPU;
  • 8 Memory Gb;
  • CentOS 6.5;

Disks:
  • 50 Gb? system;


About the PostgreSQL installation in part I will not press, as it standard.

Pgpool-II setup.
For pgpool-II setup basis, has taken the instruction from the official site: www .pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html

Would like to pay attention only the most important points:

Read more »


Migration of data from Oracle in PostgreSQL

2 years, 4 months ago
? Landscape? The DBMS in projects of our company looked so until recently: the most part was made by Oracle, significantly smaller ? MS SQL and MySQL.

But, as we know, there is nothing eternal, and recently to us the request about applicability of Postgres in one of our projects has arrived. We looked narrowly at this DBMS in the last few years very fixedly ? visited conferences, meetup? y, but here to try it in ? the fighting? conditions it did not happen until recently.

Read more »


? Under cowl? Postgres indexes

2 years, 4 months ago

The captain Nemo at steering wheel? Nautilusa?

Indexes? one of the most powerful tools in relational databases. We use them when it is necessary to find quickly some values when we integrate databases when it is necessary to accelerate work of SQL statements, etc. But what represent indexes? And how they help to accelerate search in DB? For the answer to these questions I have studied the source code PostgreSQL, having traced as there is search of index for simple line value. I expected to find difficult algorithms and effective data structures. Also has found.

Here I will tell how indexes are arranged and as they work. However I did not expect that information science is their cornerstone. The comments in code explaining not only as Postgres but also why it so works works have also helped with understanding of cherished secrets of indexes.

Read more »


PostgreSQL and btrfs — an elephant on an oil diet

2 years, 4 months ago
Recently, browsing article on Wicky about file systems, became interested in btrfs, namely its rich opportunities, the stable status and the main thing — the mechanism of transparent data compression. Knowing as the databases containing text information easily press close, it became curious to me to specify on how many it it is applicable in the scenario of use for example with postgres.

Of course it is impossible to call this testing complete because only reading and that linear is involved. But results already force to think on possible transition to btrfs in certain cases.

But the main objective — to learn opinion of community on that, on how many it it is reasonable also what reefs can conceal in itself approach of transparent compression at the file system level.

For those who do not want to spend time I will tell about the received outputs at once. PostgreSQL DB placed on btrfs with the option compress=lzo reduces DB volume in two (in comparison with any FS without compression) and when using multithreaded, consecutive reading, considerably reduces load of a disk subsystem.

Read more »


System of collecting responses of Cackle Reviews with full-text search

2 years, 4 months ago
Hi everyone! Not so long ago we have implemented full-text search for system of collecting responses of Cackle Reviews. It has turned out fine, now any moderator for some milliseconds can find the responses interesting him by the word or the sentence with stemming support (fuzzy retrieval as regards the word or to its word forms). All this works at Sphinx — system of full-text search.

On the Internet there are many articles about Sphinx, but, unfortunately, the part from them has become outdated, some other do not apply for full and exact how to. So in this post we have tried to state all steps — installations, settings, indexation and support the delta of index.

система отзывов Cackle Reviews с полнотекстовым поиском

Read more »