Richard Yen: The Curious Case of Split WAL Files
15 hours ago, postgresql

Introduction I’ve come across this scenario maybe twice in the past eight years of using Streaming Replication in Postgres: replication on a standby refuses to proceed because a WAL file has already been removed from the primary server, so it executes Plan B by attempting to fetch the relevant WAL file using restore_command (assuming that archiving is set up and working properly), but upon replay of that fetched file, we hear another croak: “No such file or directory.” Huh? The file is there? ls...

Jonathan Katz: Scheduling Backups En Masse with the Postgres Operator
18 hours ago, postgresql

An important part of running a production PostgreSQL database system (and for that matter, any database software) is to ensure you are prepared for disaster. There are many ways to go about preparing your system for disaster, but one of the simplest and most effective ways to do this is by taking periodic backups of your database clusters. How does one typically go about setting up taking a periodic backup? If you’re running PostgreSQL on a Linux based system, the solution is to often use cron,...

Bruce Momjian: Insufficient Passwords
1 day ago, postgresql

As I already mentioned, passwords were traditionally used to prove identity electronically, but are showing their weakness with increased computing power has increased and expanded attack vectors. Basically, user passwords have several restrictions: must be simple enough to remember must be short enough to type repeatedly must be complex enough to not be easily guessed must be long enough to not be easily cracked (discovered by repeated password attempts) or the number of password attempts must...

Hans-Juergen Schoenig: pg_permission: Inspecting your PostgreSQL security system
1 day ago, postgresql

Security is a super important topic. This is not only true in the PostgreSQL world – it holds true for pretty much any modern IT system. Databases, however, have special security requirements. More often than not confidential data is stored and therefore it makes sense to ensure that data is protected properly. Security first ! PostgreSQL: Listing all permissions Gaining an overview of all permissions granted to users in PostgreSQL can be quite difficult. However, if you want to secure your syst...

Luca Ferrari: PostgreSQL to Microsoft SQL Server Using TDS Foreign Data Wrapper
5 days ago, postgresql

I needed to push data from a Microsoft SQL Server 2005 to our beloved database, so why don’t use a FDW to the purpose? It has not been as simple as with other FDW, but works! PostgreSQL to Microsoft SQL Server Using TDS Foreign Data Wrapper At work I needed to push data out from a Microsoft SQL Server 2005 to a PostgreSQL 11 instance. Foreign Data Wrappers was my first thought! Perl to the rescue was my second, but since I had some time, I decided to investigate the first way first. The scenar...

Venkata Nagothi: An Overview of JSON Capabilities Within PostgreSQL
5 days ago, postgresql

What is JSON? JSON stands for “JavaScript Object Notification” which is a type of data format popularly used by web applications. This means, the data would be transmitted between web applications and servers in such a format. JSON was introduced as an alternative to the XML format. In the “good old days” the data used to get transmitted in XML format which is a heavy weight data type compared to JSON.Below is an example of JSON formatted string: { "ID":"001","name": "Ven", "Country": "Austral...

Michael Paquier: Postgres 12 highlight - SKIP_LOCKED for VACUUM and ANALYZE
6 days ago, postgresql

The following commit has been merged into Postgres 12, adding a new option for VACUUM and ANALYZE: commit: 803b1301e8c9aac478abeec62824a5d09664ffff author: Michael Paquier <michael@paquier.xyz> date: Thu, 4 Oct 2018 09:00:33 +0900 Add option SKIP_LOCKED to VACUUM and ANALYZE When specified, this option allows VACUUM to skip the work on a relation if there is a conflicting lock on it when trying to open it at the beginning of its processing. Similarly to autovacuum, this comes with a couple...

Bruce Momjian: Removable Certificate Authentication
6 days ago, postgresql

I mentioned previously that it is possible to implement certificate authentication on removable media, e.g., a USB memory stick. This blog post shows how it is done. First, root and server certificates and key files must be created: $ cd $PGDATA # create root certificate and key file $ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root.momjian.us" $ chmod og-rwx root.key $ openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_...

Craig Kerstiens: Contributing to Postgres
1 week ago, postgresql

About once a month I get this question: “How do I contribute to Postgres?”. PostgreSQL is a great database with a solid code base and for many of us, contributing back to open source is a worthwhile cause. The thing about contributing back to Postgres is you generally don’t just jump right in and commit code on day one. So figuring out where to start can be a bit overwhelming. If you’re considering getting more involved with Postgres, here’s a few tips that you may find helpful. Follow what’s ha...

Joshua Drake: CFP extended until Friday!
1 week ago, postgresql

We’ve had a great response to our PostgresConf US 2019 call for proposals with over 170 potential presentations -- thank you to everyone who has submitted so far! As with what has become a tradition among Postgres Conferences, we are extending our deadline by one week to allow those final opportunities to trickle in! The new deadline is Friday, January 18th, submit now! We accept all topics that relate to People, Postgres, Data including any Postgres related topic, such as open source tech...

Peter Eisentraut: Maintaining feature branches and submitting patches with Git
1 week ago, postgresql

I have developed a particular Git workflow for maintaining PostgreSQL feature branches and submitting patches to the pgsql-hackers mailing list and commit fests. Perhaps it’s also useful to others. This workflow is useful for features that take a long time to develop, will be submitted for review several times, and will require a significant amount of changes over time. In simpler cases, it’s probably too much overhead. You start as usual with a new feature branch off master git checkout -b rein...

Bruce Momjian: Three Factors of Authentication
1 week ago, postgresql

Traditionally, passwords were used to prove identity electronically. As computing power has increased and attack vectors expanded, passwords are proving insufficient. Multi-factor authentication uses more than one authentication factor to strengthen authentication checking. The three factors are: What you know, e.g., password, PIN What you have, e.g., cell phone, cryptographic hardware What you are, e.g., finger print, iris pattern, voice Postgres supports the first option, "What you know," na...

Achilleas Mantzios: One Security System for Application, Connection Pooling and PostgreSQL - The Case for LDAP
1 week ago, postgresql

Traditionally, the typical application consists of the following components: In this simple case, a basic setup would suffice: the application uses a simple local authentication mechanism for its users the application uses a simple connection pool there is a single user defined for database access However, as the organization evolves and gets larger more components are added: more tenant apps or instances of the app accessing the database more services and systems accessing the database cen...

Claire Giordano: 10 Most Popular Citus Data Blog Posts in 2018, ft. Postgres
1 week ago, postgresql

Seasons each have a different feel, a different rhythm. Temperature, weather, sunlight, and traditions—they all vary by season. For me, summer usually includes a beach vacation. And winter brings the smell of hot apple cider on the stove, days in the mountains hoping for the next good snowstorm—and New Year’s resolutions. Somehow January is the time to pause and reflect on the accomplishments of the past year, to take stock in what worked, and what didn’t. And of course there are the TOP TEN LIS...

Abdul Yadi: pgqr: a QR Code Generator
1 week ago, postgresql

Related with my post: https://abdulyadi.wordpress.com/2015/11/14/extension-for-qr-code-bitmap/. I have repackage the module and available on github: https://github.com/AbdulYadi/pgqr. This project adds 2 functionality to QR code generator from repository https://github.com/swex/QR-Image-embedded: In-memory monochrome bitmap construction (1 bit per pixel). Wrap the whole package as PostgreSQL extension. This project has been compiled successfully in Linux against PostgreSQL version 11. $ make c...

Quinn Weaver:
1 week ago, postgresql

In the Bay Area? This Tuesday, 2019-01-15, SFPUG features David Fetter's talk on ASSERTIONs. RSVPs close Monday at noon, so don't hesitate! Thanks to Heap for hosting at their FiDi office....

Shaun M. Thomas: PG Phriday: PgBouncer or Bust
1 week ago, postgresql

What is the role of PgBouncer in a Postgres High Availability stack? What even is PgBouncer at the end of the day? Is it a glorified traffic cop, or an integral component critical to the long-term survival of a Postgres deployment? When we talk about Postgres High Availability, a lot of terms might spring to mind. Replicas, streaming, disaster recovery, fail-over, automation; it’s a ceaseless litany of architectural concepts and methodologies. The real question is: how do we get from Here to The...

Laurenz Albe: pgbouncer authentication made easy
1 week ago, postgresql

pgbouncer is the most widely used connection pooler for PostgreSQL. This blog will provide a simple cookbook how to configure user authentication with pgbouncer. I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site. But it should work pretty similarly anywhere.   What is a connection pooler? Setting max_connections to a high value can impact performace and can even bring your database to its knees if all these connec...

Magnus Hagander: Nordic PGDay 2019 - Schedule announced and registration open
1 week ago, postgresql

Nordic PGDay 2019 is happy to announce our schedule has now been published. Our registration has also been opened. Seats are limited so we recommend that you register early if you are interested! Early Bird discount is available until February 17th or for the first 40 tickets. We look forward to seeing you in Copenhagen!...

Joshua Drake: PostgresConf 2019: CFP and Early Bird closing soon
1 week ago, postgresql

PostgresConf hopes everyone had a great holiday season and we hope everyone has a fantastic 2019. With January upon us, we start conference season. This year, PostgresConf 2019 will be in NYC at the Sheraton New York Times Square March 18-22, 2019. If you have a story about lessons learned with Postgres, a cool feature that you've found a creative way of using, or just some practical knowledge you'd like to share with your fellow Postgres users, submit a talk. Remember, there are many people w...

Next