Stephania  Von

Stephania Von

1595950320

Pg hba configuration in postgresql

In this session we are going to discuss about pg_hba.conf in postgresql

What is pg_hba file?

Hba stands for Host based Authentication

#postgresql

What is GEEK

Buddha Community

Pg hba configuration in postgresql
Stephania  Von

Stephania Von

1595950320

Pg hba configuration in postgresql

In this session we are going to discuss about pg_hba.conf in postgresql

What is pg_hba file?

Hba stands for Host based Authentication

#postgresql

PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

In our previous posts in this series, we spoke at length about using PgBouncer  and Pgpool-II , the connection pool architecture and pros and cons of leveraging one for your PostgreSQL deployment. In our final post, we will put them head-to-head in a detailed feature comparison and compare the results of PgBouncer vs. Pgpool-II performance for your PostgreSQL hosting !

The bottom line – Pgpool-II is a great tool if you need load-balancing and high availability. Connection pooling is almost a bonus you get alongside. PgBouncer does only one thing, but does it really well. If the objective is to limit the number of connections and reduce resource consumption, PgBouncer wins hands down.

It is also perfectly fine to use both PgBouncer and Pgpool-II in a chain – you can have a PgBouncer to provide connection pooling, which talks to a Pgpool-II instance that provides high availability and load balancing. This gives you the best of both worlds!

Using PgBouncer with Pgpool-II - Connection Pooling Diagram

PostgreSQL Connection Pooling: Part 4 – PgBouncer vs. Pgpool-II

CLICK TO TWEET

Performance Testing

While PgBouncer may seem to be the better option in theory, theory can often be misleading. So, we pitted the two connection poolers head-to-head, using the standard pgbench tool, to see which one provides better transactions per second throughput through a benchmark test. For good measure, we ran the same tests without a connection pooler too.

Testing Conditions

All of the PostgreSQL benchmark tests were run under the following conditions:

  1. Initialized pgbench using a scale factor of 100.
  2. Disabled auto-vacuuming on the PostgreSQL instance to prevent interference.
  3. No other workload was working at the time.
  4. Used the default pgbench script to run the tests.
  5. Used default settings for both PgBouncer and Pgpool-II, except max_children*. All PostgreSQL limits were also set to their defaults.
  6. All tests ran as a single thread, on a single-CPU, 2-core machine, for a duration of 5 minutes.
  7. Forced pgbench to create a new connection for each transaction using the -C option. This emulates modern web application workloads and is the whole reason to use a pooler!

We ran each iteration for 5 minutes to ensure any noise averaged out. Here is how the middleware was installed:

  • For PgBouncer, we installed it on the same box as the PostgreSQL server(s). This is the configuration we use in our managed PostgreSQL clusters. Since PgBouncer is a very light-weight process, installing it on the box has no impact on overall performance.
  • For Pgpool-II, we tested both when the Pgpool-II instance was installed on the same machine as PostgreSQL (on box column), and when it was installed on a different machine (off box column). As expected, the performance is much better when Pgpool-II is off the box as it doesn’t have to compete with the PostgreSQL server for resources.

Throughput Benchmark

Here are the transactions per second (TPS) results for each scenario across a range of number of clients:

#database #developer #performance #postgresql #connection control #connection pooler #connection pooler performance #connection queue #high availability #load balancing #number of connections #performance testing #pgbench #pgbouncer #pgbouncer and pgpool-ii #pgbouncer vs pgpool #pgpool-ii #pooling modes #postgresql connection pooling #postgresql limits #resource consumption #throughput benchmark #transactions per second #without pooling

Charity  Ferry

Charity Ferry

1621329666

PostgreSQL Security Hardening

Databases are the Holy Grail for hackers, and as such, must be protected with utmost care. This is the first in a series of articles in which we’ll give an overview of best practices for securing your databases. We’re starting with one of the most popular open-source databases, PostgreSQL, and will go over several levels of security you’d need to think about:

  • Network-level security
  • Transport-level security
  • Database-level security

#postgresql #postgresql security

Charity  Ferry

Charity Ferry

1622796443

Setting up a PostgreSQL Database on Mac

As per the definition from the official website – “PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads “. Postgres has been a stable and reliable database product and has been continuously under development for the past two decades. A lot of enterprise-scale applications, whether web or mobile, use Postgres Database as a backend relational database management system.

#postgresql #postgresql database

Nico Jonsson

Nico Jonsson

1602820855

How to Backup and Restore a PostgreSQL Database

In a production environment, no matter how large or small your PostgreSQL database may be, regular back is an essential aspect of database management. In this article, you will learn how to backup and restore a PostgreSQL database.

We assume that you already have a working installation of the PostgreSQL database system. If not, read our following articles to install PostgreSQL on your Linux distribution.

  • How to Install PostgreSQL and pgAdmin4 in Ubuntu 20.04
  • How to Install PostgreSQL and pgAdmin in CentOS 8
  • How to Install PostgreSQL and pgAdmin in RHEL 8

Let’s get started…

Backup a Single PostgreSQL Database

PostgreSQL provides the pg_dump utility to help you back up databases. It generates a database file with SQL commands in a format that can be easily restored in the future.

To back up, a PostgreSQL database, start by logging into your database server, then switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the name of the database you want to backup). By default, the output format is a plain-text SQL script file.

#Databases #PostgreSQL #PostgreSQL Tips