Jun
22
2020
--

Hasura launches managed cloud service for its open-source GraphQL API platform

Hasura is an open-source engine that can connect to PostgreSQL databases and microservices across hybrid- and multi-cloud environments and then automatically build a GraphQL API backend for them, making it easier for developers to then build their own data-driven applications on top of this unified API . For a while now, the San Francisco-based startup has offered a paid version (Hasura Pro) with enterprise-ready reliability and security tools, in addition to its free open-source version. Today, the company launched Hasura Cloud, which takes the existing Pro version, adds a number of cloud-specific features like dynamic caching, auto-scaling and consumption-based pricing, and brings those together in a fully managed service.

Image Credits: Hasura

At its core, Hasura’s service promises businesses the ability to bring together data from their various siloed databases and allow their developers to extract value from them through its GraphQL APIs. While GraphQL is still relatively new, the Facebook-incubated technology has quickly become extremely popular among many development teams.

Before founding the company and launching it in 2018, Hasura CEO and co-founder Tanmai Gopal worked for a consulting firm — and like with so many founders, that’s where he got the inspiration for the service.

“One of the key things that we noticed was that in the entire landscape, computing is becoming better, there are better frameworks, it is easier to deploy code, databases are becoming better and they kind of work everywhere,” he said. “But this kind of piece in the middle that is still a bottleneck and that there isn’t really a good solution for is this data access piece.” Almost by default, most companies host data in various SaaS services and databases — and now they were trying to figure out how to develop apps based on this for both internal and external consumers, noted Gopal. “This data distribution problem was this bottleneck where everybody would just spend massive amounts of time and money. And we invented a way of kind of automating that,” he explained.

The choice of GraphQL was also pretty straightforward, especially because GraphQL services are an easy way for developers to consume data (even though, as Gopal noted, it’s not always fun to build the GraphQL service itself). One thing that’s unusual and worth noting about the core Hasura engine itself is that it is written in Haskell, which is a rather unusual choice.

Image Credits: Hasura

The team tells me that Hasura is now nearing 50 million downloads for its free version and the company is seeing large and small users from across various industries relying on its products, which is probably no surprise, given that the company is trying to solve a pretty universal problem around data access and consumption.

Over the last few quarters, the team worked on launching its cloud service. “We’ve been thinking of the cloud in a very different way,” Gopal said. “It’s not your usual, take the open-source solution and host it, like a MongoDB Atlas or Confluent. What we’ve done is we’ve said, we’re going to re-engineer the open-source solution to be entirely multi-tenant and be completely pay-per pricing.”

Given this philosophy, it’s no surprise that Hasura’s pricing is purely based on how much data a user moves through the service. “It’s much closer to our value proposition,” Hasura co-founder and COO Rajoshi Ghosh said. “The value proposition is about data access. The big part of it is the fact that you’re getting this data from your databases. But the very interesting part is that this data can actually come from anywhere. This data could be in your third-party services, part of your data could be living in Stripe and it could be living in Salesforce, and it could be living in other services. […] We’re the data access infrastructure in that sense. And this pricing also — from a mental model perspective — makes it much clearer that that’s the value that we’re adding.”

Now, there are obviously plenty of other data-centric API services on the market, but Gopal argues that Hasura has an advantage because of its advanced caching for dynamic data, for example.

May
19
2020
--

Microsoft launches Azure Synapse Link to help enterprises get faster insights from their data

At its Build developer conference, Microsoft today announced Azure Synapse Link, a new enterprise service that allows businesses to analyze their data faster and more efficiently, using an approach that’s generally called “hybrid transaction/analytical processing” (HTAP). That’s a mouthful; it essentially enables enterprises to use the same database system for analytical and transactional workloads on a single system. Traditionally, enterprises had to make some trade-offs between either building a single system for both that was often highly over-provisioned or maintain separate systems for transactional and analytics workloads.

Last year, at its Ignite conference, Microsoft announced Azure Synapse Analytics, an analytics service that combines analytics and data warehousing to create what the company calls “the next evolution of Azure SQL Data Warehouse.” Synapse Analytics brings together data from Microsoft’s services and those from its partners and makes it easier to analyze.

“One of the key things, as we work with our customers on their digital transformation journey, there is an aspect of being data-driven, of being insights-driven as a culture, and a key part of that really is that once you decide there is some amount of information or insights that you need, how quickly are you able to get to that? For us, time to insight and a secondary element, which is the cost it takes, the effort it takes to build these pipelines and maintain them with an end-to-end analytics solution, was a key metric we have been observing for multiple years from our largest enterprise customers,” said Rohan Kumar, Microsoft’s corporate VP for Azure Data.

Synapse Link takes the work Microsoft did on Synaps Analytics a step further by removing the barriers between Azure’s operational databases and Synapse Analytics, so enterprises can immediately get value from the data in those databases without going through a data warehouse first.

“What we are announcing with Synapse Link is the next major step in the same vision that we had around reducing the time to insight,” explained Kumar. “And in this particular case, a long-standing barrier that exists today between operational databases and analytics systems is these complex ETL (extract, transform, load) pipelines that need to be set up just so you can do basic operational reporting or where, in a very transactionally consistent way, you need to move data from your operational system to the analytics system, because you don’t want to impact the performance of the operational system in any way because that’s typically dealing with, depending on the system, millions of transactions per second.”

ETL pipelines, Kumar argued, are typically expensive and hard to build and maintain, yet enterprises are now building new apps — and maybe even line of business mobile apps — where any action that consumers take and that is registered in the operational database is immediately available for predictive analytics, for example.

From the user perspective, enabling this only takes a single click to link the two, while it removes the need for managing additional data pipelines or database resources. That, Kumar said, was always the main goal for Synapse Link. “With a single click, you should be able to enable real-time analytics on your operational data in ways that don’t have any impact on your operational systems, so you’re not using the compute part of your operational system to do the query, you actually have to transform the data into a columnar format, which is more adaptable for analytics, and that’s really what we achieved with Synapse Link.”

Because traditional HTAP systems on-premises typically share their compute resources with the operational database, those systems never quite took off, Kumar argued. In the cloud, with Synapse Link, though, that impact doesn’t exist because you’re dealing with two separate systems. Now, once a transaction gets committed to the operational database, the Synapse Link system transforms the data into a columnar format that is more optimized for the analytics system — and it does so in real time.

For now, Synapse Link is only available in conjunction with Microsoft’s Cosmos DB database. As Kumar told me, that’s because that’s where the company saw the highest demand for this kind of service, but you can expect the company to add support for available in Azure SQL, Azure Database for PostgreSQL and Azure Database for MySQL in the future.

Nov
20
2019
--

Proposal for Global Indexes in PostgreSQL

Global Indexes in PostgreSQL

PostgreSQLA global index, by very definition, is a single index on the parent table that maps to many underlying table partitions. The parent table itself does not have a single, unified underlying store so it must, therefore, retrieve the data satisfying index constraints from physically distributed tables. In very crude terms, the global index accumulates data in one place so that data spanning across multiple partitions are accessed in one go as opposed to individually querying each partition.

Currently, there is no Global Index implementation available in PostgreSQL, and therefore I want to propose a new feature.  I have sent a proposal to the community, and that discussion is now started. In this proposal, I ask for Global Index support just for B-Tree and will consider other index methods later.

Terminologies used

  • Global Indexes

 A one-to-many index, in which one index map to all the partitioned tables. 

  • Partitioned Index (Index Partitioning)

When global indexes become too large, then those are partitioned to keep the performance and maintenance overhead manageable. These are not within the scope of this work.

  • Local Index

A local index is an index that is local to a specific table partition; i.e. it doesn’t span across multiple partitions. So, when we create an index on a parent table, it will create a separate index for all its partitions. PostgreSQL uses the terminology of “partitioned index” when it refers to local indexes. This work will fix this terminology for PostgreSQL so that the nomenclature remains consistent with other DBMS.

Why Do We Need Global Index in PostgreSQL?

A global index is expected to give two very important upgrades to the partitioning feature set in PostgreSQL. It is expected to give a significant improvement in read-performance for queries targeting multiple local indexes of partitions, as well as adding a unique constraint across partitions.

Unique Constraint

Data uniqueness is a critical requirement for building an index. For global indexes that span across multiple partitions, uniqueness will have to be enforced on index column(s). This effectively translates into a unique constraint.

Performance

Currently, the pseudo index created on the parent table of partitions does not contain any data. Rather, it dereferences to the local indexes when an index search is required. This means that multiple indexes will have to be evaluated with data to be combined thereafter. However, with the global indexes, data will reside with the global index declared on the parent table. This avoids the need for multi-level index lookups, so read performance is expected to be significantly higher in some cases. There will, however, be a negative performance impact during write (insert/update) of data. This is discussed in more detail later on.

Creating a Global Index – Syntax

A global index may be created with the addition of a “GLOBAL” keyword to the index statement. Alternatively, one could specify the “LOCAL” keyword to create local indexes on partitions. We are suggesting to call this set of keywords: “partition_index_type”. By default, partition_index_type will be set as LOCAL. Here is a sample of the create index syntax.

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]

    ( { column_name | ( expression ) } [LOCAL | GLOBAL] [ COLLATE collation ] 
    [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

    [ INCLUDE ( column_name [, ...] ) ]

    [ WITH ( storage_parameter = value [, ... ] ) ]

    [ TABLESPACE tablespace_name ]

    [ WHERE predicate ]

Pointing Index to Tuple

Currently, CTID carries a page and offset information for a known heap (table name). However, in the context of global indexes, this information within an index is insufficient. Since the index is expected to carry tuples from multiple partitions (heaps), CTID alone will not be able to link an index node to a tuple. This requires carrying additional data for the heap name to be stored with each index node.

Optimizer

The challenge with optimizer is a selection between local and global indexes when both are present. There have been many open questions, including evaluating the cost of scanning a global index. When should the LOCAL index be preferred over the GLOBAL index and vice versa?

Write Performance and Vacuum

There will be some write performance degradation because every change in partition tables must propagate upwards to the GLOBAL index on the parent table. This can be thought of as another index on a table, however, the [slight] performance degradation will be due to the fact that the GLOBAL index may carry a much bigger dataset with data from multiple partitions resulting in a higher tree traversal and update time. This applies to both write and vacuum processes.

It is still an open question, though, on how this will be handled within the code and how we can better optimize this process.

Conclusion

As we know most major DBMS engines that have partitioning support also have support for the Global Index. PostgreSQL has very powerful partitioning support but lacks the support of the Global Index. Global Index not only ensures the uniqueness across partitioning but also improves read performance.  I have sent the proposal to PostgreSQL Community and while a discussion has been started, it is a slow process. If you are an engineer and want to contribute, respond to that thread in the community. If you are a user and have some uses cases, please share that on the same mail chain. 

Nov
08
2019
--

Configure HAProxy with PostgreSQL Using Built-in pgsql-check

HAProxy PostgreSQL pgsql-check

PostgreSQLWe discussed one of the traditional ways to configure HAProxy with PostgreSQL in our previous blog about HAProxy using Xinetd. There we briefly mentioned the limitation of the HAProxy’s built-in pgsql-check health check option. It lacks features to detect and differentiate the Primary and Hot-Standby. It tries to establish a connection to the database instance and if the connection request is progressing, it will be considered as a successful check and there is no provision to check the current role (Primary or Standby).

So the question remains:

  1. Is the HAProxy’s built-in pgsql-check completely useless as it cannot distinguish between a Primary and a hot-standby (standby that accepts reads) in an HA setup?
  2. Is there a way to tweak pgsql-check so that it can distinguish between a Primary and Hot-standby?

This blog post discusses what is possible using pgsql-check and how to achieve that.

Note: This blog demonstrates the concept.  Integration with specific HA framework/script is left to users because there are a large number of HA solutions for PostgreSQL and these concepts are equally applicable for them

Concept

When a client initiates the connection to PostgreSQL, the first stage of a check is whether it is acceptable as per rules specified in pg_hba.conf.  That stage needs to be completed before proceeding to the next stage of specific authentication mechanisms.

The pgsql-check is designed to check this first stage (pg_hba.conf) and return success if it is passed because a positive response from the server can be considered as a litmus test for whether the instance is up and capable of accepting connections. It doesn’t have to complete the authentication. pgsql-check abandons the connection after this check before completing the initial handshakes and PostgreSQL terminates it.

A connection request will be straight away rejected if pg_hba.conf rule says to “reject” it. For example, a pg_hba.conf entry like

host    postgres    pmm_user    192.168.80.20/32    reject

tells PostgreSQL to reject connection from IP 192.168.80.20 as pmm_user to postgres database.

We can use this logic of modifying the pg_hba.conf entries as part of the failover/switchover procedure. Such automation is easily achievable by a callback script if you are using any kind of automation for failover or switchover. Alternatively, we can have a small script that checks the database instance status in each node and maintains the pg_hbha.conf entry accordingly.

In a nutshell, routing tables in HAProxy can get automatically modified and connections will be routed according to pg_hba.conf entries which are maintained as part of HA scripts/solution OR switchover/failover procedure.

Demonstration Setup

I have one primary and two hot-standby servers with server nodes (pg0, pg1, pg2) and an application server (app). We shall create two users; one for the Primary connection (read-write) detection and another for standby (read-only) connection detection.

postgres=# create user primaryuser with password 'primaryuser';
CREATE ROLE
postgres=# create user standbyuser with password 'standbyuser';
CREATE ROLE

Now we need to have pg_hba.conf entry such a way that connection request to this user will be taken forward for authentication.

host    primaryuser    primaryuser    192.168.50.0/24    md5
host    standbyuser    standbyuser    192.168.50.0/24    md5

Please see that the username and database names are kept as same. because the default name of the database is the same as user. This will help with the straightaway rejection of connection which is what we want rather than later reporting that

database "xyz" does not exist

.  We should keep in mind that there are NO such databases that exist in this PostgreSQL cluster with the name “primaryuser” or “standbyuser”. So this user won’t be really able to connect to any database even if we are not rejecting it. This is an added security to the whole setup.

We should reload the configuration:

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

It is a good idea to verify the client connection from the application server to this user.

$ psql -h pg0 -U primaryuser
Password for user primaryuser:
psql: FATAL: database "primaryuser" does not exist

Here we can see that connection request is taken forward for authentication, so it prompts for the password but the connection will be rejected finally because there is no such database as “primaryuser”. This is sufficient for HAProxy configuration.

We need to have the same setup for all the nodes of the PostgreSQL cluster because any node can be promoted to primary or demoted to standby.

Preparing HAProxy

We are going to have two ports open in haproxy for connection.

  1. Port 5000 for Primary Connections (Read-Write)
  2. Port 5001 for Standby Connections (Read-Only)

Here is the sample haproxy configuration (/etc/haproxy/haproxy.cfg) I used in the demonstration setup

global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen pgReadWrite
    bind *:5000
    option pgsql-check user primaryuser
    default-server inter 3s fall 3
    server pg0 pg0:5432 check port 5432
    server pg1 pg1:5432 check port 5432
    server pg2 pg2:5432 check port 5432

listen pgReadOnly
    bind *:5001
    option pgsql-check user standbyuser
    default-server inter 3s fall 3
    server pg0 pg0:5432 check port 5432
    server pg1 pg1:5432 check port 5432
    server pg2 pg2:5432 check port 5432

Note: haproxy installation and default configuration file location might change based on OS. On Redhat clones, installation is generally as simple as running: $ sudo yum install haproxy

As we can see in the configuration, the check is using option pgsql-check with user primaryuser for “pgReadWrite” connections and standbyuser for “pgReadOnly” connections which are intended for Primary and Standby connections respectively. All three nodes of the cluster are candidates for all the types of connections.

Once we have the configuration ready, the haproxy service can be started up.

$ sudo systemctl start haproxy

At this stage, all nodes will be listed as candidates for both read-write and read-only connections which will be marked in the green background color.

This is not what we want to achieve.

Integration of pg_hba.conf with failover / switchover procedure

All HA solutions for failover/switchover have the provision for housekeeping which updates configuration files like recovery.conf and capability for callback custom scripts. It is the responsibility of the same failover/switchover procedure to make changes to pg_hba.conf in this case. The change should be such a way that the pg_hba.conf setting should reject the standbyuser connection on the primary node and primaryuser connection from standby servers. For this demonstration, I am directly modifying the authentication method “reject”.

On Primary:

$ sed -i 's/\(host\s*standbyuser\s*standbyuser.*\) md5/\1 reject/g' $PGDATA\pg_hba.conf

So that the line will change to:

host    standbyuser    standbyuser    192.168.50.0/24    reject

On Standby:

$ sed -i 's/\(host\s*primaryuser\s*primaryuser.*\) md5/\1 reject/g' $PGDATA\pg_hba.conf

So that the line will change to:

host    primaryuser    primaryuser    192.168.50.0/24    reject

After reloading the configuration, the routing tables will get updated with the right set of information

This is what we need to have.

Testing

Testing of this HAProxy setup can be done from the machine (app) where HAProxy is currently configured.

Connections to port 5000 will be routed to Primary:

$ psql -h localhost -U postgres -p 5000 -c "select pg_is_in_recovery()"
Password for user postgres:
pg_is_in_recovery
-------------------
f
(1 row)

Connection to port 5001 will be routed to one of the standby:

$ psql -h localhost -U postgres -p 5001 -c "select pg_is_in_recovery()"
Password for user postgres:
pg_is_in_recovery
-------------------
t
(1 row)

So with the help of a custom callback script which modifies the pg_hba.conf, HAProxy can maintain its routing table and thereby redirect the connections.

Advantages and Disadvantages of pgsql-check

The advantage of built-in pgsql-check is obvious in that we don’t need any extra components and setup is very straight-forward.

On the disadvantage side, the modification of pg_hba.conf is the key and we should make sure that it is getting updated as part of failover and switchover.

As we described in the concept, pgsql-check is designed to abandon the connections after the check which PostgreSQL will be logging as:

LOG: could not receive data from client: Connection reset by peer

Moreover, all the connection requests are rejected at the pg_bha.conf rule will be logged as well:

FATAL: pg_hba.conf rejects connection for host "192.168.50.40", user "primaryuser", database "primaryuser", SSL off

PostgreSQL log file will be containing a lot of such messages which you can ignore, but it will be nice if you are expecting the log files to be clean.

Special Note:- There are some recent improvements to HAProxy like this commit which improves the disconnection of pgsql-check. So messages like

LOG: could not receive data from client: Connection reset by peer

may not appear in the logs. This is tested and confirmed with HAProxy Version 2.0.8. However, at the time of writing this blog, it is not available in most of the repositories and you may have to build it from source code.

Oct
31
2019
--

PostgreSQL Application Connection Failover Using HAProxy with xinetd

PostgreSQL Application Connection Failover Using HAProxy with xinetd

PostgreSQLRecently we published a blog about a very simple application failover using libpq features which could be the simplest of all automatic application connection routing.  In this blog post, we are discussing how a proxy server using HAProxy can be used for connection routing which is a well-known technique with very wide deployment. There are multiple ways HAProxy can be configured with PostgreSQL which we shall cover in upcoming blogs, but configuring a xinetd service to respond to Http requests on individual nodes of a database cluster is one of the most traditional approaches.

On HAProxy

HAProxy could be the most popular connection routing and load balancing software available. Along with PostgreSQL, it is used across different types of High Availability Clusters. HAProxy, as the name indicates, works as a proxy for TCP (Layer 4) and HTTP (Layer 7), but it has additional features of load balancing also. The TCP proxying feature allows us to use it for database connections of PostgreSQL. There are three objectives of connection routing of a PostgreSQL cluster:

  1. Read-Write load to Master
  2. Read-Only load to Slave
  3. Load balancing of multiple slaves is achievable by HAProxy.

HAProxy maintains an internal routing table. In this blog, we are going to take a look at the most traditional approach to configure HAProxy with PostgreSQL. This approach is independent of underlying clustering software and can be used even with the traditional PostgreSQL built-in replication feature without any clustering or automation solutions.

In this generic configuration, we won’t use any special software or capabilities offered by clustering frameworks. This requires us to have 3 components:

  1. A simple shell script to check the status of the PostgreSQL instance running on the local machine.
  2. A xinetd service daemonizer.
  3. HAProxy: Which maintains the routing mechanism.

Concept:

HAProxy has a built-in check for PostgreSQL with option pgsql-check.  (Documentation is available here) This is good enough for basic Primary failover. But the lack of features to detect and differentiate the Primary and Hot-Standby nodes makes it less useful.

Meanwhile, HAProxy with xinetd would give us the luxury to see what is the Master and what is a hot standby to redirect connections appropriately. We will be writing about the built-in check pgsql-check in upcoming blog posts and explain how to make use of it effectively.

Xinetd (Extended Internet Service Daemon) is a Super-server daemon. It can listen to requests on custom ports and respond to requests by executing custom logic. In this case, we have custom scripts to check the status of the database. In the script we use writes HTTP header with status code. Different status code represents the status of the database instance. Status code 200 if PostgreSQL instance is Primary, 206 if PostgreSQL is Hot Standby, and 503 if status cannot be verified.

Every database server needs to have a xinetd service running on a port for status checks of PostgreSQL instances running in them. Generally, port: 23267 is used for this purpose, but we can use any port of our choice. This service uses a custom-developed script (shell script) to understand the 3 different statuses of PostgreSQL instances.

  1. Primary database
  2. Standby database
  3. Unable to connect to PostgreSQL – Indication of PostgreSQL down

Since the status check is available through a port exposed by xinetd, HAProxy can send a request to that port and understand the status from the response.

Installation and Configuration

First, we need to have a script that can check the status of a PostgreSQL instance. It is quite simple, the shell script invokes psql utility and executes pg_is_in_recovery() function of postgres. Based on the result, it can understand whether it is a master or slave or whether it failed to connect.

A sample script is here:

#!/bin/bash
# This script checks if a postgres server is healthy running on localhost. It will return:
# "HTTP/1.x 200 OK\r" (if postgres is running smoothly)
# - OR -
# "HTTP/1.x 500 Internal Server Error\r" (else)
# The purpose of this script is make haproxy capable of monitoring postgres properly
# It is recommended that a low-privileged postgres  user is created to be used by this script.
# For eg. create  user healthchkusr login password 'hc321';
 
PGBIN=/usr/pgsql-10/bin
PGSQL_HOST="localhost"
PGSQL_PORT="5432"
PGSQL_DATABASE="postgres"
PGSQL_USERNAME="postgres"
export PGPASSWORD="passwd"
TMP_FILE="/tmp/pgsqlchk.out"
ERR_FILE="/tmp/pgsqlchk.err"
 
 
# We perform a simple query that should return a few results
 
VALUE=`/opt/bigsql/pg96/bin/psql -t -h localhost -U postgres -p 5432 -c "select pg_is_in_recovery()" 2> /dev/null`
# Check the output. If it is not empty then everything is fine and we return something. Else, we just do not return anything.
 
 
if [ $VALUE == "t" ]
then
    /bin/echo -e "HTTP/1.1 206 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "Standby"
    /bin/echo -e "\r\n"
elif [ $VALUE == "f" ]
then
    /bin/echo -e "HTTP/1.1 200 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "Primary"
    /bin/echo -e "\r\n"
else
    /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "DB Down"
    /bin/echo -e "\r\n"
fi

Instead of password-based authentication, any password-less authentication methods can be used.

It is a good practice to keep the script in /opt folder, but make sure that it has got execute permission:

$ sudo chmod 755 /opt/pgsqlchk

Now we can install xinetd on the server. Optionally, we can install a telnet client so that we can test the functionality.

$ sudo yum install -y xinetd telnet

Now let us create a xinetd definition/configuration.

$ sudo vi /etc/xinetd.d/pgsqlchk

Add a configuration specification to the same file as below:

service pgsqlchk
{
        flags           = REUSE
        socket_type     = stream
        port            = 23267
        wait            = no
        user            = nobody
        server          = /opt/pgsqlchk
        log_on_failure  += USERID
        disable         = no
        only_from       = 0.0.0.0/0
        per_source      = UNLIMITED
}

Add the pgsqlchk service to /etc/services.

$ sudo bash -c 'echo "pgsqlchk 23267/tcp # pgsqlchk" >> /etc/services'

Now xinetd service can be started.

$ sudo systemctl start xinetd

Configuring HAProxy to use xinetd

We need to have HAProxy installed on the server:

$ sudo yum install -y haproxy

Create or modify the HAProxy configuration. Open /etc/haproxy/haproxy.cfg using a text editor.

$ sudo vi /etc/haproxy/haproxy.cfg

A sample HAProxy configuration file is given below:

global
    maxconn 100
 
defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s
 
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
 
listen ReadWrite
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg0 pg0:5432 maxconn 100 check port 23267
    server pg1 pg1:5432 maxconn 100 check port 23267
 
listen ReadOnly
    bind *:5001
    option httpchk
    http-check expect status 206
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg0 pg0:5432 maxconn 100 check port 23267
    server pg1 pg1:5432 maxconn 100 check port 23267

As per the above configuration, the key points to note are

  • HAProxy is configured to use TCP mode
  • HAProxy service will start listening to port 5000 and 5001
  • Port 5000 is for Read-Write connections and 5001 is for Read-Only connections
  • Status check is done using http-check feature on port 23267
  • Both server pg0 and pg1 are candidates for both Read-write and Read-only connections
  • Based on the http-check and the status returned, it decides the current role

Now everything is set for starting the HAProxy service.

$ sudo systemctl start haproxy

Verification and Testing

As per HAProxy configuration, we should be able to access the port 5000 for a read-write connection.

$ psql -h localhost -p 5000 -U postgres
Password for user postgres:
psql (9.6.5)
Type "help" for help.

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

For read-only connection, we should be able to access the port 5001:

$ psql -h localhost -p 5001 -U postgres
Password for user postgres:
psql (9.6.5)
Type "help" for help.

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

Conclusion

This is a very generic way of configuring HAProxy with a PostgreSQL cluster, but it’s not limited to any particular cluster topology. Healthcheck is done by a custom shell script and the result of the health check is available through xinetd service. HAProxy uses this information for maintaining the routing table and redirecting the connection to the appropriate node in the cluster.

Oct
29
2019
--

Monitoring PostgreSQL Databases Using PMM

Monitoring PostgreSQL with Percona Monitoring Management

PostgreSQLPostgreSQL is a widely-used Open Source database and has been the DBMS of the year for the past 2 years in DB-Engine rankings. As such, there is always a need for reliable and robust monitoring solutions. While there are some commercial monitoring tools, there is an equally good number of open source tools available for monitoring PostgreSQL. Percona Monitoring and Management (PMM) is one of those open source solutions that have continuous improvements and is maintained forever by Percona. It is simple to set up and easy to use.

PMM can monitor not only PostgreSQL but also MySQL and MongoDB databases, so it is a simple monitoring solution for monitoring multiple types of databases. In this blog post, you will see all the steps involved in monitoring PostgreSQL databases using PMM.

This is what we will be discussing:

  1. Using the PMM docker image to create a PMM server.
  2. Installing PMM client on a Remote PostgreSQL server and connecting the PostgreSQL Client to PMM Server.
  3. Creating required users and permissions on the PostgreSQL server.
  4. Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

If you already know how to create a PMM Server, please skip the PMM server setup and proceed to the PostgreSQL client setup.

Using the PMM docker image to create a PMM server

PMM is a client-server architecture where clients are the PostgreSQL, MySQL, or MongoDB databases and the server is the PMM Server. We see a list of metrics on the Grafana dashboard by connecting to the PMM server on the UI. In order to demonstrate this setup, I have created 2 virtual machines where one of them is the PMM Server and the second server is the PostgreSQL database server.

192.168.80.10 is my PMM-Server
192.168.80.20 is my PG 11 Server

Step 1 : 

On the PMM Server, install and start docker.

# yum install docker -y
# systemctl start docker

Here are the installation instructions of PMM Server.

Step 2 :

Pull the pmm-server docker image. I am using the latest PMM2 docker image for this setup.

$ docker pull percona/pmm-server:2

You see a docker image of size 1.48 GB downloaded after the above step.

$ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/percona/pmm-server 2 cd30e7343bb1 2 weeks ago 1.48 GB

Step 3 :

Create a container for persistent PMM data.

$ docker create \
-v /srv \
--name pmm-data \
percona/pmm-server:2 /bin/true

Step 4 :

Create and launch the PMM Server. In the following step, you can see that we are binding the port 80 of the container to the port 80 of the host machine. Likewise for port 443.

$ docker run -d \
-p 80:80 \
-p 443:443 \
--volumes-from pmm-data \
--name pmm-server \
--restart always \
percona/pmm-server:2

At this stage, you can modify certain settings such as the memory you wish to allocate to the container or the CPU share, etc. You can also see more such configurable options using

docker run --help

. The following is just an example of how you can modify the above step with some memory or CPU allocations.

$ docker run -d \
-p 80:80 \
-p 443:443 \
--volumes-from pmm-data \
--name pmm-server \
--cpu-shares 100 \
--memory 1024m \
--restart always \
percona/pmm-server:2

You can list the containers started for validation using

docker ps

.

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bb6043082d3b percona/pmm-server:2 "/opt/entrypoint.sh" About a minute ago Up About a minute 0.0.0.0:80->80/tcp, 0.0.0.0:443->443/tcp pmm-server

Step 5 : 

You can now see the PMM Server Dashboard in the browser using the Host IP address. For my setup, the PMM Server’s IP Address is

192.168.80.10

. As soon as you put the IP in the browser, you will be asked to enter the credentials as seen in the image below. Default user and password are both:

admin

create a PMM server

And then you will be asked to change the password or skip.

PMM Server setup is completed after this step.

Installing PMM client on a Remote PostgreSQL server

I have a PostgreSQL 11.5 Server running on

192.168.80.20

. The following steps demonstrate how we can install and configure the PMM client to enable monitoring from the PMM server (

192.168.80.10

).

Before you proceed further, you must ensure that ports 80 and 443 are both enabled on the PMM server for the PG 11 Server to connect. In order to test that, I have used telnet to validate whether ports 80 and 443 are open on the PMM Server for the pg11 server.

[root@pg11]$ hostname -I
192.168.80.20

[root@pg11]$ telnet 192.168.80.10 80
Trying 192.168.80.10...
Connected to 192.168.80.10.
Escape character is '^]'.

[root@pg11]$ telnet 192.168.80.10 443
Trying 192.168.80.10...
Connected to 192.168.80.10.
Escape character is '^]'.

Step 6 :

There are very few steps you need to perform on the PostgreSQL server to enable it as a client for PMM server. The first step is to install the PMM Client on the PostgreSQL Database server as follows. Based on the current PMM release, I am installing

pmm2-client

today. But, this may change once we have a new PMM release.

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ sudo yum install pmm2-client -y

Step 7 :

The next step is to connect the client (PostgreSQL server) to the PMM Server. We could use

pmm-admin config

in order to achieve that. Following is a simple syntax that you could use in general.

$ pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

The following are the flags and other options I could use with my setup.

flags        : --server-insecure-tls
               --server-url=https://admin:admin@192.168.80.10:443
               (--server-url should contain the PMM Server Host information)

node-address : 192.168.80.20
               (My PostgreSQL Server)

node-type    : generic
               (As I am running my PostgreSQL database on a Virtual Machine but not on a Container, it is generic.)

node-name    : pg-client
               (Can be any nodename you could use to uniquely identify this database server on your PMM Server Dashboard)

So the final syntax for my setup looks like the below. We can run this command as root or by using the sudo command.

Syntax : 7a

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.80.10:443 192.168.80.20 generic pg-client

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.80.10:443 192.168.80.20 generic pg-client
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Syntax : 7b

You could also use a simple syntax such as following without

node-address, node-type, node-name

 :

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.80.10:443

But when you use such a simple syntax as above,

node-address, node-type, node-name

are defaulted to certain values. If the defaults are incorrect due to your server configuration, you may better pass these details explicitly like I have done in the

syntax : 7a

. In order to validate whether the defaults are correct, you can simply use

# pmm-admin config --help

. In the following log, you see that the

node-address

  defaults to

10.0.2.15

which is incorrect for my setup. It should be

192.168.80.20

.

# pmm-admin config --help
usage: pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

Configure local pmm-agent

Flags:
  -h, --help                   Show context-sensitive help (also try --help-long and --help-man)
      --version                Show application version
...
...
...
Args:
  [<node-address>]  Node address (autodetected default: 10.0.2.15)

Below is an example where the default settings were perfect because I had configured my database server the right way.

# pmm-admin config --help
usage: pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

Configure local pmm-agent

Flags:
  -h, --help                   Show context-sensitive help (also try --help-long and --help-man)
...
...
Args:
  [<node-address>]  Node address (autodetected default: 192.168.80.20)
  [<node-type>]     Node type, one of: generic, container (default: generic)
  [<node-name>]     Node name (autodetected default: pg-client)

Using steps 6 and 7a, I have finished installing the PMM client on the PostgreSQL server and also connected it to the PMM Server. If the above steps are successful, you should see the client listed under Nodes, as seen in the following image. Else, something went wrong.

Creating required users and permissions on the PostgreSQL server

In order to monitor your PostgreSQL server using PMM, you need to create a user *using* which the database stats can be collected by the PMM agent. However, starting from PostgreSQL 10, you do not need to grant SUPERUSER or use SECURITY DEFINER (to avoid granting SUPERUSER). You can simply grant the role

pg_monitor

to a user (monitoring user). In my next blog post, you will see how we could use SECURITY DEFINER to avoid granting SUPERUSER for monitoring PostgreSQL databases with 9.6 or older.

Assuming that your PostgreSQL Version is 10 or higher, you can use the following steps.

Step 1 : 

Create a postgres user that can be used for monitoring. You could choose any username;

pmm_user

in the following command is just an example.

$ psql -c "CREATE USER pmm_user WITH ENCRYPTED PASSWORD 'secret'"

Step 2 : 

Grant

pg_monitor

role to the

pmm_user

.

$ psql -c "GRANT pg_monitor to pmm_user"

Step 3 : 

If you are not using localhost, but using the IP address of the PostgreSQL server while enabling monitoring in the next steps, you should ensure to add appropriate entries to enable connections from the

IP

and the

pmm_user

 in the

pg_hba.conf

file.

$ echo "host    all             pmm_user        192.168.80.20/32        md5" >> $PGDATA/pg_hba.conf
$ psql -c "select pg_reload_conf()"

In the above step, replace

192.168.80.20

with the appropriate PostgreSQL Server’s IP address.

Step 4 : 

Validate whether you are able to connect as

pmm_user

to the postgres database from the postgres server itself.

# psql -h 192.168.80.20 -p 5432 -U pmm_user -d postgres
Password for user pmm_user: 
psql (11.5)
Type "help" for help.

postgres=>

Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

Using PMM, we can monitor several metrics in PostgreSQL such as database connections, locks, checkpoint stats, transactions, temp usage, etc. However, you could additionally enable Query Analytics to look at the query performance and understand the queries that need some tuning. Let us see how we can simply enable PostgreSQL monitoring with and without QAN.

Without QAN

Step 1 :

In order to start monitoring PostgreSQL, we could simply use

pmm-admin add postgresql

. It accepts additional arguments such as the service name and PostgreSQL address and port. As we are talking about enabling monitoring without QAN, we could use the flag:

--query-source=none

to disable QAN.

# pmm-admin add postgresql --query-source=none --username=pmm_user --password=secret postgres 192.168.80.20:5432
PostgreSQL Service added.
Service ID  : /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea
Service name: postgres

Step 2 :

Once you have enabled monitoring, you could validate the same using

pmm-admin list

.

# pmm-admin list
Service type  Service name         Address and port  Service ID
PostgreSQL    postgres             192.168.80.20:5432 /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/13fd2e0a-a01a-4ac2-909a-cae533eba72e  
node_exporter               running    /agent_id/f6ba099c-b7ba-43dd-a3b3-f9d65394976d  
postgres_exporter           running    /agent_id/1d046311-dad7-467e-b024-d2c8cb7f33c2  /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea

You can now access the PostgreSQL Dashboards and see several metrics being monitored.

With QAN

With PMM2, there is an additional step needed to enable QAN. You should create a database with the same name as the monitoring user (

pmm_user

here). And then, you should create the extension:

pg_stat_statements

in that database. This behavior is going to change on the next release so that you can avoid creating the database.

Step 1 : 

Create the database with the same name as the monitoring user. Create the extension:

pg_stat_statements

in the database.

$ psql -c "CREATE DATABASE pmm_user"
$ psql -c -d pmm_user "CREATE EXTENSION pg_stat_statements"

Step 2 : 

If

shared_preload_libraries

has not been set to

pg_stat_statements

, we need to set it and restart PostgreSQL.

$ psql -c "ALTER SYSTEM SET shared_preload_libraries TO 'pg_stat_statements'"
$ pg_ctl -D $PGDATA restart -mf
waiting for server to shut down.... done
server stopped
...
...
 done
server started

Step 3 :

In the previous steps, we used the flag:

--query-source=none

to disable QAN. In order to enable QAN, you could just remove this flag and use

pmm-admin add postgresql

without the flag.

# pmm-admin add postgresql --username=pmm_user --password=secret postgres 192.168.80.20:5432
PostgreSQL Service added.
Service ID  : /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
Service name: postgres

Step 4 : 

Once the above step is completed, you could validate the same again using

pmm-admin list

. But this time, you should see an additional service:

qan-postgresql-pgstatements-agent

.

# pmm-admin list
Service type  Service name         Address and port  Service ID
PostgreSQL    postgres             192.168.80.20:5432 /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/13fd2e0a-a01a-4ac2-909a-cae533eba72e  
node_exporter               running    /agent_id/f6ba099c-b7ba-43dd-a3b3-f9d65394976d  
postgres_exporter           running    /agent_id/7039f7c4-1431-4518-9cbd-880c679513fb  /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
qan-postgresql-pgstatements-agent running    /agent_id/7f0c2a30-6710-4191-9373-fec179726422  /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73

After this step, you can now see the Queries and their statistics captured on the

Query Analytics Dashboard

.

Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. PMM works for both Community PostgreSQL and also the Percona Distribution for PostgreSQL. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.

Oct
23
2019
--

Seamless Application Failover using libpq Features in PostgreSQL

libpq features in postgresql

PostgreSQLWhen you build replication in PostgreSQL using Streaming replication, you cannot perform writes to a standby node; only reads. This way, you could offload reads or reporting queries to standby servers and send writes to master. Additionally, starting from PostgreSQL 10,

libpq

  and

psql

clients could probe the connection for a master and allow connections to a master for

read-write

or

any

node for

read-only

connections automatically.

For example, consider three database nodes – Server_A, Server_B, and Server_C in replication using streaming replication, with Server_A being the Master/Primary node. You could specify all three servers in a connection string and request the connection to be redirected to a

read-write

node only, which is Server_A in this scenario. If a failover or a switchover happened to Server_B, the

read-write

connections will be automatically redirected to Server_B. To understand this in detail, let us see a simple scenario in action.

I have set up a three-node replication cluster using streaming replication with the following roles.

192.168.70.10 is the master
192.168.70.20 is the first standby
192.168.70.30 is the second standby

$psql -h 192.168.70.10
Password for user postgres:
psql (11.5)
Type "help" for help.

postgres=# select inet_server_addr() as "connected_to";
connected_to
---------------
192.168.70.10
(1 row)

postgres=# select client_addr, write_lag,flush_lag,replay_lag from pg_stat_replication;
client_addr | write_lag | flush_lag | replay_lag
---------------+-----------------+-----------------+-----------------
192.168.70.20 | 00:00:00.058204 | 00:00:00.058617 | 00:00:00.058619
192.168.70.30 | 00:00:00.03639 | 00:00:00.036689 | 00:00:00.036693
(2 rows)

Now, let us use

psql

with all the three IPs specified in the connection string. We would, however, use

target_session_attrs

this time to connect to a master node.

Connecting to Master Using Read-Write Mode

$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=read-write' -c "select inet_server_addr()"
Password for user postgres:
inet_server_addr
------------------
192.168.70.10
(1 row)

Connecting to any Server for Reads

Please note that the server that is first in the list is automatically connected when you used target_session_attrs as

any

 .

$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"
inet_server_addr
------------------
192.168.70.20
(1 row)

Or

$ psql 'postgres://192.168.70.10:5432,192.168.70.20:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"
inet_server_addr
------------------
192.168.70.10
(1 row)

If the server that is first in the list is not reachable, the driver tries to connect to the next server in the list for reads. So, a reads connection would never fail when you have multiple standbys and at least one of the database nodes is reachable while using target_session_attrs as “any”.

-- On Server : 192.168.70.10

$ pg_ctl -D $PGDATA stop -mf
waiting for server to shut down.... done
server stopped
[postgres@pg1]$ psql 'postgres://192.168.70.10:5432,192.168.70.20:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"
inet_server_addr
------------------
192.168.70.20
(1 row)

An important point to note is that the driver might take additional time in connecting to each node in the list to determine if it is a master. Let’s say that the server:

192.168.70.10

is no longer a master and

192.168.70.20

(second in the list of servers in the connection string) is the new master accepting writes. When you specify that the connections should go to a

read-write

node, the driver checks if the first server in the list accepts writes and then connects to the second server. If the first server is not reachable, then you may experience further delay. However, this is still a seamless failover as you do not have to disturb the application during this switchover.

Let us say that you use

Python

or

PHP

to connect to PostgreSQL. As the application interfaces for

Python

,

PHP

, and several other programming languages use

libpq

as the underlying engine, you could use multiple IPs in the connection string and request the connections be redirected to a

read-write

or

any

node.

Below is an example to achieve this with Python. I have a written a simple python script and specified

target_session_attrs

as

"read-write"

by passing multiple IPs to the host. Now, when I execute the script, it confirms the IP connected to (192.168.70.10 is a master here) and shows that the server is not in a

recovery

mode.

$ cat pg_conn.py
import psycopg2
conn = psycopg2.connect(database="postgres",host="192.168.70.10,192.168.70.20,192.168.70.30", user="postgres", password="secret", port="5432", target_session_attrs="read-write")
cur = conn.cursor()
cur.execute("select pg_is_in_recovery(), inet_server_addr()")
row = cur.fetchone()
print "recovery =",row[0]
print "server =",row[1]

$ python pg_conn.py
recovery = False
server = 192.168.70.10

I could similarly use PHP to connect to postgres and specify that the connections should only be directed to a master node as seen in the following example.

# cat pg_conn.php
<?php
$conn = pg_connect("host=192.168.70.10,192.168.70.20,192.168.70.30 port=5432 dbname=postgres user=postgres password=secret target_session_attrs=read-write") or die("Could not connect");
$status = pg_connection_status($conn);
if ($status === PGSQL_CONNECTION_OK) {
print "Connection status ok\n";
} else {
print "Connection status bad\n";
}
$sql = pg_query($conn, "select pg_is_in_recovery()");
while ($row = pg_fetch_row($sql)) {
echo "Recovery-status: $row[0]\n";
}
?>

$ php -f pg_conn.php
Connection status ok
Recovery-status: f
Server: 192.168.70.10

An important point to note is that the clients are able to achieve this because they are using

libpq

that belongs to PG10 or later.

# yum info python2-psycopg2-2.8.3-2.rhel7.x86_64 | grep repo
From repo : pgdg11

# rpm -q --requires python2-psycopg2-2.8.3-2.rhel7.x86_64 | grep libpq
libpq.so.5()(64bit)

# rpm -q --requires php-pgsql-5.4.16-46.el7 | grep libpq
libpq.so.5()(64bit)

# locate libpq.so.5
/usr/pgsql-11/lib/libpq.so.5

We have discussed that you might expect some slowness due to multiple hops while connecting to an appropriate master server, but this approach still helps for a seamless application failover. And we have discussed the built-in mechanism available with Community PostgreSQL by default. In the next blog post, Jobin Augustine will be talking about using HAProxy (Open Source) for achieving a much more robust and reliable way to perform a seamless application failover with PostgreSQL.

Oct
11
2019
--

How to Set Up Streaming Replication in PostgreSQL 12

Streaming Replication in PostgreSQL

PostgreSQLPostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements, planner improvements, several SQL features, Indexing improvements, etc. You may see some of such features discussed in future blog posts. But, let me start this blog with something interesting. You might have already seen some news that there is no

recovery.conf

file in standby anymore and that the replication setup (streaming replication) has slightly changed in PostgreSQL 12. We have earlier blogged about the steps involved in setting up a simple Streaming Replication until PostgreSQL 11 and also about using replication slots for the same. Let’s see how different is it to set up the same Streaming Replication in PostgreSQL 12.

Installing PostgreSQL 12 on Master and Standby

On CentOS/RedHat, you may use the rpms available in the PGDG repo (the following link may change depending on your OS release).

# as root:
yum install -y https://yum.postgresql.org/12/redhat/rhel-7.4-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install -y postgresql12-server

Steps to set up Streaming Replication in PostgreSQL 12

In the following steps, the Master server is: 192.168.0.108 and the Standby server is: 192.168.0.107

Step 1 :
Initialize and start PostgreSQL, if not done already on the Master.

## Preparing the environment
$ sudo su - postgres
$ echo "export PATH=/usr/pgsql-12/bin:$PATH PAGER=less" >> ~/.pgsql_profile
$ source ~/.pgsql_profile

## As root, initialize and start PostgreSQL 12 on the Master
$ /usr/pgsql-12/bin/postgresql-12-setup initdb
$ systemctl start postgresql-12

 

Step 2 :
Modify the parameter

listen_addresses

to allow a specific IP interface or all (using *). Modifying this parameter requires a restart of the PostgreSQL instance to get the change into effect.

# as postgres
$ psql -c "ALTER SYSTEM SET listen_addresses TO '*'";
ALTER SYSTEM

# as root, restart the service
$ systemctl restart postgresql-12

You may not have to set any other parameters on the Master for simple replication setup, because the defaults hold good.

 

Step 3 :
Create a User for replication in the Master. It is discouraged to use superuser postgres in order to setup replication, though it works.

postgres=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret';
CREATE ROLE

 

Step 4 :
Allow replication connections from Standby to Master by appending a similar line as following to the

pg_hba.conf

file of the Master. If you are enabling automatic failover using any external tool, you must also allow replication connections from Master to the Standby. In the event of a failover, the Standby may be promoted as a Master and the Old Master need to replicate changes from the New Master (previously a standby). You may use any of the authentication methods as supported by PostgreSQL today.

$ echo "host replication replicator 192.168.0.107/32 md5" >> $PGDATA/pg_hba.conf

## Get the changes into effect through a reload.

$ psql -c "select pg_reload_conf()"

 

Step 5 :
You may use

pg_basebackup

  to backup the data directory of the Master from the Standby. While creating the backup, you may also tell

pg_basebackup

  to create the replication specific files and entries in the data directory using

"-R"

 .

## This command must be executed on the standby server.
$ pg_basebackup -h 192.168.0.108 -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R
Password:
25314/25314 kB (100%), 1/1 tablespace

You may use multiple approaches such as rsync or any other disk backup methods to copy the master’s data directory to the standby. But, there is an important file (standby.signal) that must exist in a standby data directory to help postgres determine its state as a standby. It is automatically created when you use the

"-R"

option while taking

pg_basebackup

. If not, you may simply use touch to create this empty file.

$ touch $PGDATA/standby.signal

$ ls -l $PGDATA
total 60
-rw-------. 1 postgres postgres 224 Oct 8 16:41 backup_label
drwx------. 5 postgres postgres 41 Oct 8 16:41 base
-rw-------. 1 postgres postgres 30 Oct 8 16:41 current_logfiles
drwx------. 2 postgres postgres 4096 Oct 8 16:41 global
drwx------. 2 postgres postgres 32 Oct 8 16:41 log
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_commit_ts
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_dynshmem
-rw-------. 1 postgres postgres 4581 Oct 8 16:41 pg_hba.conf
-rw-------. 1 postgres postgres 1636 Oct 8 16:41 pg_ident.conf
drwx------. 4 postgres postgres 68 Oct 8 16:41 pg_logical
drwx------. 4 postgres postgres 36 Oct 8 16:41 pg_multixact
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_notify
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_replslot
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_serial
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_snapshots
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_stat
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_stat_tmp
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_subtrans
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_tblspc
drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_twophase
-rw-------. 1 postgres postgres 3 Oct 8 16:41 PG_VERSION
drwx------. 3 postgres postgres 60 Oct 8 16:41 pg_wal
drwx------. 2 postgres postgres 18 Oct 8 16:41 pg_xact
-rw-------. 1 postgres postgres 288 Oct 8 16:41 postgresql.auto.conf
-rw-------. 1 postgres postgres 26638 Oct 8 16:41 postgresql.conf
-rw-------. 1 postgres postgres 0 Oct 8 16:41 standby.signal

One of the most important observations should be the contents of the

postgresql.auto.conf

file in the standby server. As you see in the following log, an additional parameter

primary_conninfo

has been added to this file. This parameter tells the standby about its Master. If you haven’t used

pg_basebackup

with

-R

option, you would not see this entry (of

primary_conninfo

) in this file, on the standby server. Which means that you have to add this manually.

$ cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
primary_conninfo = 'user=replicator password=secret host=192.168.0.108 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

postgresql.auto.conf

file is the configuration file that is read at the end when you start Postgres. So, if there is a parameter that has different values in

postgresql.conf

and

postgresql.auto.conf

files, the value set in the

postgresql.auto.conf

is considered by PostgreSQL. Also, any parameter that has been modified using

ALTER SYSTEM

would automatically be written to

postgresql.auto.conf

  file by postgres.

How was the replication configuration handled until PostgreSQL 11?

Until PostgreSQL 11, we must create a file named:

recovery.conf

that contains the following minimalistic parameters. If the

standby_mode

is ON, it is considered to be a standby.

$ cat $PGDATA/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.8 port=5432 user=replicator password=secret'

So the first difference between PostgreSQL 12 and earlier (until PostgreSQL 11) is that the

standby_mode

parameter is not present in PostgreSQL 12 and the same has been replaced by an empty file

standby.signal

in the standby’s data directory. And the second difference is the parameter

primary_conninfo

. This can now be added to the

postgresql.conf

or

postgresql.auto.conf

 file of the standby’s data directory.

 

Step 6 :
Start PostgreSQL using

pg_ctl

on the Standby.

$ pg_ctl -D $PGDATA start

 

Step 7 :
Verify the replication between the Master and the Standby. In order to verify, run this command on the Master. In the following log, you see a lot of details of the standby and the lag between the Master and Standby.

$ psql -x -c "select * from pg_stat_replication"
-[ RECORD 1 ]----+------------------------------
pid | 2522
usesysid | 16384
usename | replicator
application_name | walreceiver
client_addr | 192.168.0.107
client_hostname |
client_port | 36382
backend_start | 2019-10-08 17:15:19.658917-04
backend_xmin |
state | streaming
sent_lsn | 0/CB02A90
write_lsn | 0/CB02A90
flush_lsn | 0/CB02A90
replay_lsn | 0/CB02A90
write_lag | 00:00:00.095746
flush_lag | 00:00:00.096522
replay_lag | 00:00:00.096839
sync_priority | 0
sync_state | async
reply_time | 2019-10-08 17:18:04.783975-04

Enabling Archiving on Master and the Standby recovery using Archives.

Most of the time, the default or modified retention settings of WAL segments on the Master may not be enough to maintain a healthy replication between itself and its standby. So, we need the WALs to be safely archived to another disk or a remote backup server. These archived WAL segments can be used by the standby to replay them when the WALs are gone from the Master.

To enable archiving on the Master, we can still use the same approach of setting the following 2 parameters.

archive_mode = ON
archive_command = 'cp %p /archives/%f' ## Modify this with an appropriate shell command.

But to enable recovery from archives on a standby, we used to add a parameter named

restore_command

to the

recovery.conf

file until PostgreSQL 11. But starting from PostgreSQL 12, we can add the same parameter to

postgresql.conf

or

postgresql.auto.conf

file of the standby. Please note that it requires a restart of PostgreSQL to update the changes made to

archive_mode

and

restore_command

parameters.

echo "restore_command = 'cp /archives/%f %p'" >> $PGDATA/postgresql.auto.conf
pg_ctl -D $PGDATA restart -mf

In my next blog post, I shall talk about Point-in-time-recovery on PostgreSQL 12, where I will discuss a few more parameters related to recovery in detail. Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.

Discuss on HackerNews

Oct
09
2019
--

Percona Monitoring and Management (PMM) 2.0.1 Is Now Available

Percona Monitoring and Management 2.0.1

Percona Monitoring and Management 2.0.1Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring your database performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL®, MariaDB®, MongoDB®, and PostgreSQL® servers to ensure that your data works as efficiently as possible.

In this release, we are introducing the following PMM enhancements:

  • Securely share Dashboards with Percona – let Percona engineers see what you see!
  • Improved navigation – Now PMM remembers which host and service, and applies these as filters when navigating to Query Analytics

Securely share Dashboards with Percona

A dashboard snapshot is a way to securely share what you’re seeing, with Percona. When created, we strip sensitive data like queries (metrics, template variables, and annotations) along with panel links. The shared dashboard will only be available for Percona engineers to view as it will be protected by Percona’s two-factor authentication system. The content on the dashboard will assist Percona engineers in troubleshooting your case.

Improved navigation

Now when you transition from looking at metrics into Query Analytics, PMM will remember the host and service, and automatically apply these as filters:

Improvements

  • PMM-4779: Securely share dashboards with Percona
  • PMM-4735: Keep one old slowlog file after rotation
  • PMM-4724: Alt+click on check updates button enables force-update
  • PMM-4444: Return “what’s new” URL with the information extracted from the pmm-update package changelog

Fixed bugs

  • PMM-4758: Remove Inventory rows from dashboards
  • PMM-4757qan_mysql_perfschema_agent failed querying events_statements_summary_by_digest due to data types conversion
  • PMM-4755: Fixed a typo in the InnoDB AHI Miss Ratio formula
  • PMM-4749: Navigation from Dashboards to QAN when some Node or Service was selected now applies filtering by them in QAN
  • PMM-4742: General information links were updated to go to PMM 2 related pages
  • PMM-4739: Remove request instances list
  • PMM-4734: A fix was made for the collecting node_name formula at MySQL Replication Summary dashboard
  • PMM-4729: Fixes were made for formulas on MySQL Instances Overview
  • PMM-4726: Links to services in MongoDB singlestats didn’t show Node name
  • PMM-4720machine_id could contain trailing \n
  • PMM-4640: It was not possible to add MongoDB remotely if password contained a # symbol

Help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

Sep
19
2019
--

A Guide to Installing Percona Monitoring and Management (PMM)2 For the First Time

Percona Monitoring and Management 2

Percona Monitoring and Management 2Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL®, MongoDB®, and PostgreSQL® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL, MongoDB, and PostgreSQL servers to ensure that your data works as efficiently as possible.

Please see our previous blog for full information on the new features included in PMM2, our latest software release.

To use PMM2 you will need to remove any earlier versions of PMM Server and Client as there is no in-place upgrade path for historical data, and then download and run our latest software.

Install PMM Server with docker

The easiest way to install PMM Server is to deploy it with Docker. Running the PMM2 Docker container with PMM Server can be done by the following commands (note the version tag of 2):

docker create -v /srv --name pmm-data percona/pmm-server:2 /bin/true
docker run -d -p 80:80 -p 443:443 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:2

Now you can check the newly installed PMM Server in your browser, going to your server by its IP address and using the default credentials:

username: admin

password: admin

You will be prompted to change the admin password after logging in.

Install PMM Client

You can get PMM2 Client package from the official download page on the Percona web site, or use the official percona-release package from Percona to enable the tools repository, as follows (please see percona-release official documentation for further details on this new tool).

Ubuntu/Debian instructions

Install the Percona repository:

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

Install the pmm2-client package:

apt-get update
apt-get install pmm2-client

Red Hat/CentOS Instructions

Install the Percona repository:

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Install the pmm2-client package:

sudo yum update
sudo yum install pmm2-client

Users who have previously installed pmm2-client pre-release versions should fully remove the package and then install the latest available version.

Configure PMM

Once PMM Client is installed, run the pmm-admin config command with your PMM Server IP address in order to register with the PMM Server:

Note that you need to pass the authentication string as part of the --server-url. These credentials are the same ones you used to login to PMM Server.

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP Address>:443

You should see the following:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Adding MySQL Metrics and Query Analytics

MySQL server can be added for the monitoring in its normal way. Here is a command which adds it using the PERFORMANCE_SCHEMA source:

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pmm

where username and password are credentials for the monitored MySQL access, which will be used locally on the database host.

The syntax to add MySQL services (Metrics and Query Analytics) using the Slow Log source is the following:

pmm-admin add mysql --query-source=slowlog --username=pmm --password=pmm

When the server is added, you can check your MySQL dashboards and Query Analytics in order to view its performance information!

Adding MongoDB Metrics and Query Analytics

Please refer to PMM 2 documentation to find out how to set up the required permissions and enable profiling with MongoDB.

You can add MongoDB services (Metrics and Query Analytics) with a similar command:

pmm-admin add mongodb --username=pmm --password=pmm

Adding PostgreSQL Metrics and Query Analytics

Please refer to PMM2 documentation to find out how to add PostgreSQL extension for queries monitoring, as well as set up the required user permissions and authentication.

Add PostgreSQL monitoring to your PMM Server with the following command:

pmm-admin add postgresql --username=pmm --password=pmm

Where username and password parameters should contain actual PostgreSQL user credentials.

Add ProxySQL monitoring service

You can add ProxySQL service as follows:

pmm-admin add proxysql --username=admin --password=admin

Please help us improve our software quality by reporting any Percona Monitoring and Management bugs you encounter using our bug tracking system.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com