フィードアグリゲーター

Douglas Hunley: pgBouncer and auth pass-thru

planet postgresql - 2018-08-07(火) 21:53:28

I’ve noticed several individuals inquiring lately about pgBouncer and how they can avoid putting all users and their passwords in it’s auth_file. After the most recent such inquiry (hi Richard!) I decided I’d write this post to hopefully make it clearer how to use ‘pass-thru auth’ and avoid maintaining your users and their passwords in an external file. So let’s see what this takes, shall we?

First, install pgBouncer as per your OS (yum, apt, brew, etc):

doug@ReturnOfTheMac ~> brew install pgbouncer Updating Homebrew... ==> Auto-updated Homebrew! Updated 1 tap (homebrew/core). ==> Updated Formulae <snip> ==> Downloading https://homebrew.bintray.com/bottles/pgbouncer-1.8.1.high_sierra ######################################################################## 100.0% ==> Pouring pgbouncer-1.8.1.high_sierra.bottle.tar.gz ==> Caveats The config file: /usr/local/etc/pgbouncer.ini is in the "ini" format and you will need to edit it for your particular setup. See: https://pgbouncer.github.io/config.html The auth_file option should point to the /usr/local/etc/userlist.txt file which can be populated by the /usr/local/opt/pgbouncer/bin/mkauth.py script. To have launchd start pgbouncer now and restart at login: brew services start pgbouncer Or, if you do not want/need a background service you can just run: pgbouncer -q /usr/local/etc/pgbouncer.ini ==> Summary 🍺 /usr/local/Cellar/pgbouncer/1.8.1: 17 files, 399.9KB

Great, so now we have pgBouncer installed.

To make life easier on ourselves, we’re going to temporarily enable trusted local socket connections in our pg_hba.conf:

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust

Right now, this is the only line in my pg_hba.conf. Let’s SIGHUP the postmaster so it takes affect:

doug@ReturnOfTheMac ~> pg_ctl -D $PGDATA reload server signaled

And test it:

doug@ReturnOfTheMac ~> unset PGPASSWORD ; psql -U doug -d doug -c "select now();" ┌────────────────────────[...]
カテゴリー: postgresql

Krzysztof Książek: Monitoring your Databases with ClusterControl

planet postgresql - 2018-08-07(火) 19:42:29

Observability is critical piece of the operations puzzle - you have to be able to tell the state of your system based on data. Ideally, this data will be available from a single location. Having multiple applications, each handling separate pieces of data, is a direct way to serious troubles. When the issues start, you have to be able to tell what is going on quickly rather than trying to analyze and merge reports from multiple sources.

ClusterControl, among other features, provides users with a one single point from which to track the health of your databases. In this blog post, we will show some of the observability features in ClusterControl.

Overview Tab

Overview section is a one place to track the state of one cluster, including all the cluster nodes as well as any load balancers.

It provides easy access to multiple pre-defined dashboards which show the most important information for the given type of cluster. ClusterControl supports different open source datastores, and different graphs are displayed based on the vendor. ClusterControl also gives an option to create your own, custom dashboards:

One key feature is that graphs are aggregated across all cluster nodes. This makes it easier to track the state of the whole cluster. If you want to check graphs from each of the nodes, you can easily do that:

By ticking “Show Servers”, all nodes in the cluster will be shown separately allowing you to drill down into each one of them.

Nodes Tab

If you would like to check a particular node in more details, you can do so from the “Nodes” tab.

Here you can find metrics related to given host - CPU, disk, network, memory. All the important bits of data which define how a given server behaves and how loaded it is.

Nodes tab also gives you an option to check the database metrics for a given node:

All of those graphs are customizable, you can easily add more of them:

Nodes tab also contains metrics related to nodes other than databases. For example, for ProxySQL, ClusterControl provides extensive list of graphs to t

[...]
カテゴリー: postgresql

Luca Ferrari: pgxnclient and beta version

planet postgresql - 2018-08-07(火) 09:00:00

pgxnclient is a wonderful cpan like tool for the PGXN extension network. Unlickily, the client cannot handle PostgreSQL beta version, so I submitted a really small patch to fix the issue.

pgxnclient and beta version

If you, like me, are addicted to terminal mode, you surely love a tool like pgxnclient that allows you to install extension into PostgreSQL from the command line, much like cpan (and friends) does for Perl.

A few days ago, I run into a problem: the `load** command cannot work against a PostgreSQL 11 beta 2 server. At first I reported it with a [ticket])https://github.com/dvarrazzo/pgxnclient/issues/29), but then curiosity hit me and I decided to give a look at very well written source code.

Warning: I’m not a Python developer, or better, I’m a Python-idiot! This means the work I’ve done, even if it seems it works, could be totally wrong, so reviews are welcome.

First I got to the regular expression used to parse a version() output:

m = re.match(r'\S+\s+(\d+)\.(\d+)(?:\.(\d+))?', data)

where data is the output of a SELECT version();. Now, this works great for a version like 9.6.5 or 10.3, but does not work for 11beta2. Therefore, I decided to implement a two level regular expression check: at first search for a two or three numbers, and if it fails, search for two numbers separated by the beta text string.

m = re.match(r'\S+\s+(\d+)\.(\d+)(?:\.(\d+))?', data) if m is None: m = re.match( r'\S+\s+(\d+)beta(\d+)', data ) is_beta...
カテゴリー: postgresql

Avinash Kumar: Basic Understanding of Bloat and VACUUM in PostgreSQL

planet postgresql - 2018-08-06(月) 22:46:06

Implementation of MVCC (Multi-Version Concurrency Control) in PostgreSQL is different and special when compared with other RDBMS. MVCC in PostgreSQL controls which tuples can be visible to transactions via versioning.

What is versioning in PostgreSQL?

Let’s consider the case of an Oracle or a MySQL Database. What happens when you perform a DELETE or an UPDATE of a row? You see an UNDO record maintained in a global UNDO Segment. This UNDO segment contains the past image of a row, to help database achieve consistency. (the “C” in A.C.I.D). For example, if there is an old transaction that depends on the row that got deleted, the row may still be visible to it because the past image is still maintained in the UNDO. If you are an Oracle DBA reading this blog post, you may quickly recollect the error

ORA-01555 snapshot too old  . What this error means is—you may have a smaller undo_retention or not a huge UNDO segment that could retain all the past images (versions) needed by the existing or old transactions.

You may not have to worry about that with PostgreSQL.

Then how does PostgreSQL manage UNDO ?

In simple terms, PostgreSQL maintains both the past image and the latest image of a row in its own Table. It means, UNDO is maintained within each table. And this is done through versioning. Now, we may get a hint that, every row of PostgreSQL table has a version number. And that is absolutely correct. In order to understand how these versions are maintained within each table, you should understand the hidden columns of a table (especially xmin) in PostgreSQL.

Understanding the Hidden Columns of a Table

When you describe a table, you would only see the columns you have added, like you see in the following log.

percona=# \d scott.employee Table "scott.employee" Column | Type | Collation | Nullable | Default ----------+------------------------+-----------+----------+------------------------------------------------ emp_id | integer [...]
カテゴリー: postgresql

brian davis: Cost of a Join

planet postgresql - 2018-08-05(日) 14:00:00

How expensive is a join?

It depends! It depends what the join criteria is, what indexes are present, how big the tables are, whether the relations are cached, what hardware is being used, what configuration parameters are set, whether statistics are up-to-date, what other activity is happening on the system, to name a few things.

But we can still try and get a feel for what a couple simple scenarios look like and see what happens when:

  1. The number of tables being joined increases
  2. The number of rows in those tables increases
  3. Indexes are present / not present

My inspiration is a question that comes up over and over again when designing the schema for an upcoming feature. For example, if we have an existing table of products and we want to add the notion of a "status" for each product. "Active", "Discontinued", "Recalled", etc. Do we:

  1. Add a status_id column to the product table and reference a new status table
  2. Add a status_id column to the product table and let the app define the mapping of what each status_id is
  3. Add a status column of type text to the product table

I usually argue for the first option. Arguments for options two and three usually revolve around two points. Concerns over the performance of the join, and developer ergonomics. The developer ergonomics is a matter of taste, but we can certainly look at the join performance.

We'll go nuts and really put PostgreSQL to the test. For some straightforward equi-joins - the kind a lookup table would be doing - let's see what happens to performance when the number of joins goes through the roof. How many is too many?

Here's how we'll be creating our tables for testing. Scroll down to the charts if you just want to see the results.

DROP FUNCTION IF EXISTS create_tables(integer, integer, boolean); CREATE FUNCTION create_tables(num_tables integer, num_rows integer, create_indexes boolean) RETURNS void AS $function_text$ BEGIN -- There's no table before the first one, so this one's a little different. Create it here instead of in our loop. DROP TABLE IF EXIST[...]
カテゴリー: postgresql

Ozgun Erdogan: Citus 7.5: The right way to scale SaaS apps

planet postgresql - 2018-08-04(土) 03:18:00

One of the primary challenges with scaling SaaS applications is the database. While you can easily scale your application by adding more servers, scaling your database is a way harder problem. This is particularly true if your application benefits from relational database features, such as transactions, table joins, and database constraints.

At Citus, we make scaling your database easy. Over the past year, we added support for distributed transactions, made Rails and Django integration seamless, and expanded on our SQL support. We also documented approaches to scaling your SaaS database to thousands of customers.

Today, we’re excited to announce the latest release of our distributed database—Citus 7.5. With this release, we’re adding key features that make scaling your SaaS / multi-tenant database easier. If you’re into bulleted lists, these features include the following.

What’s New in Citus 7.5
  • Foreign key constraints from distributed to reference tables
  • SELECT .. FOR UPDATE
  • Per-customer (per-tenant) query statistics (aka Landlord)
  • Advanced Security: Row and column level access controls
  • Native integration with PostgreSQL extensions: HLL and TopN

To try these new features, you can download Citus packages on your local machine or create a Citus distributed database cluster on Citus Cloud. Or, keep reading to learn more about 7.5.

Example SaaS Application

Let’s write an ad analytics app which companies can use to view, change, and analyze their ads. Such an application has the characteristics of a typical SaaS / multi-tenant application. Data from different tenants is stored in a central database, and each tenant has an isolated view of their data.

First, you create two distributed tables using the standard PostgreSQL syntax.

CREATE TABLE companies ( id bigint NOT NULL, name text NOT NULL, image_url text, CONSTRAINT pk_company_id PRIMARY KEY (id) ); CREATE TABLE campaigns ( id bigint NOT NULL, company_id bigint NOT NULL, country_id int NOT NULL, name text NOT NULL, cost_mo[...]
カテゴリー: postgresql

PHP 7.3.0.beta1 Released

php.net - 2018-08-02(木) 18:44:58
カテゴリー: php

Brian Fehrle: Understanding And Reading the PostgreSQL System Catalog

planet postgresql - 2018-08-02(木) 16:01:09

Managing databases is no small task, and can easily be frustrating without knowing what’s happening under the covers. Whether trying to find out if new indexes are helpful, the transaction count on a database at a certain time, or who’s connected to the database at any given time, data that allows the administrators truly know how their databases are performing is king. Luckily, with PostgreSQL, that data for all of this is available in the PostgreSQL system catalog.

The PostgreSQL System Catalog is a schema with tables and views that contain metadata about all the other objects inside the database and more. With it, we can discover when various operations happen, how tables or indexes are accessed, and even whether or not the database system is reading information from memory or needing to fetch data from disk.

Here we will go over an overview of the system catalog, and highlight how to read it, and how to pull useful information from it. Some of the metadata is straightforward, and other pieces take a bit of digesting to generate real useful information. Either way, PostgreSQL gives us a great platform to build whatever information we need about the database itself.

The PostgreSQL Catalog

PostgreSQL stores the metadata information about the database and cluster in the schema ‘pg_catalog’. This information is partially used by PostgreSQL itself to keep track of things itself, but it also is presented so external people / processes can understand the inside of the databases too.

The PostgreSQL Catalog has a pretty solid rule: Look, don’t touch. While PostgreSQL stores all this information in tables like any other application would, the data in the tables are fully managed by PostgreSQL itself, and should not be modified unless an absolute emergency, and even then a rebuild is likely in order afterwards.

We will go over a few useful catalog tables, how to read the data, and clever things we can do with the data itself. There are quite a few tables in the catalog that we won’t go over, but all information for the

[...]
カテゴリー: postgresql

David Wheeler: pgenv

planet postgresql - 2018-08-02(木) 13:31:03

For years, I’ve managed multiple versions of PostgreSQL by regularly editing and running a simple script that builds each major version from source and installs it in /usr/local. I would shut down the current version, remove the symlink to /usr/local/pgsql, symlink the one I wanted, and start it up again.

This is a pain in the ass.

Recently I wiped my work computer (because reasons) and started reinstalling all my usual tools. PostgreSQL, I decided, no longer needs to run as the postgres user from /usr/local. What would be much nicer, when it came time to test pgTAP against all supported versions of Postgres, would be to use a tool like plenv or rbenv to do all the work for me.

So I wrote pgenv. To use it, clone it into ~/.pgenv (or wherever you want) and add its bin directories to your $PATH environment variable:

git clone https://github.com/theory/pgenv.git echo 'export PATH="$HOME/.pgenv/bin:$HOME/.pgenv/pgsql/bin:$PATH"' >> ~/.bash_profile

Then you’re ready to go:

pgenv build 10.4

A few minutes later, it’s there:

$ pgenv versions pgsql-10.4

Let’s use it:

$ pgenv use 10.4 The files belonging to this database system will be owned by user "david". This user must also own the server process. # (initdb output elided) waiting for server to start.... done server started PostgreSQL 10.4 started

Now connect:

$ psql -U postgres psql (10.4) Type "help" for help. postgres=#

Easy. Each version you install – as far back as 8.0 – has the default super user postgres for compatibility with the usual system-installed version. It also builds all contrib modules, including PL/Perl using /usr/bin/perl.

With this little app in place, I quickly built all the versions I need. Check it out:

$ pgenv versions pgsql-10.3 * pgsql-10.4 pgsql-11beta2 pgsql-8.0.26 pgsql-8.1.23 pgsql-8.2.23 pgsql-8.3.23 pgsql-8.4.22 pgsql-9.0.19 pgsql-9.1.24 pgsql-9.2.24 pgsql-9.3.23 pgsql-9.4.18 pgsql-9.5.13 pgsql-9.6.9

Other commands include start, stop, and restart, which

[...]
カテゴリー: postgresql

Jonathan Katz: Using the CIS PostgreSQL Benchmark to Enhance Your Security

planet postgresql - 2018-08-01(水) 23:02:30

Crunchy Data recently announced the publication of the CIS PostgreSQL Benchmark by the Center for Internet Security, a nonprofit organization that provides publications around standards and best practices for securing technologies systems. This CIS PostgreSQL Benchmark builds on earlier work that Crunchy started when it helped to publish the PostgreSQL Security Technical Implementation Guide (PostgreSQL STIG) and provides guidance and steps to help secure your PostgreSQL databases.

What is a CIS Benchmark?

A CIS Benchmark is a set of guidelines and best practices for securely configuring a target system. Authoring a CIS Benchmark is a collaborative process as CIS involves considerable peer reviews and discussion before a major version is published, to ensure there is a general consensus on the best practices for deploying a secure system.

カテゴリー: postgresql

Sebastian Insausti: New Webinar: An Introduction to Performance Monitoring for PostgreSQL

planet postgresql - 2018-08-01(水) 22:51:46

Join our webinar on August 21st during which we’ll dive into monitoring PostgreSQL for performance.

PostgreSQL offers many metrics through various status overviews and commands, but which ones really matter to you?

How do you trend and alert on them? What is the meaning behind the metrics? And what are some of the most common causes for performance problems in production?

To operate PostgreSQL efficiently, you need to have insight into database performance and make sure it is at optimal levels.

During this webinar, we’ll discuss this and more in ordinary, plain DBA language. We’ll also have a look at some of the tools available for PostgreSQL monitoring and trending; and we’ll show you how to leverage ClusterControl’s PostgreSQL metrics, dashboards, custom alerting and other features to track and optimize the performance of your system.

Date, Time & Registration Europe/MEA/APAC

Tuesday, August 21st at 09:00 BST / 10:00 CEST (Germany, France, Sweden)

Register Now

North America/LatAm

Tuesday, August 21st at 09:00 Pacific Time (US) / 12:00 Eastern Time (US)

Register Now

Agenda
  • PostgreSQL architecture overview
  • Performance problems in production
    • Common causes
  • Key PostgreSQL metrics and their meaning
  • Tuning for performance
  • Performance monitoring tools
  • Impact of monitoring on performance
  • How to use ClusterControl to identify performance issues
    • Demo
Speaker

Sebastian Insausti has loved technology since his childhood, when he did his first computer course (Windows 3.11). And from that moment he was decided on what his profession would be. He has since built up experience with MySQL, PostgreSQL, HAProxy, WAF (ModSecurity), Linux (RedHat, CentOS, OL, Ubuntu server), Monitoring (Nagios), Networking and Virtualization (VMWare, Proxmox, Hyper-V, RHEV).

Prior to joining Severalnines, Sebastian worked as a consultant to state companies in security, database replication and high availability scenarios. He’s also a speaker and has given a few talks locally on InnoDB Cluster and MySQL Enterprise together with an Ora

[...]
カテゴリー: postgresql

ページ