Find Database DNS / Endpoint via SQL

How to get Database identifier using SQL

Often there is a need for client programs to find "where am I logged into?". This blog post provides a few ways to know more to answer this question. 

When more and more applications are breaking out of the regular (database + application) architecture, this gets more and more important in scenarios when you're not 100% sure "where" you just logged into.

Some common scenarios, where this is helpful are:

- Proxy between application and Database

- Load Balancer (application Load balancers & Database Load Balancers)

- Serverless Database Clusters

- Auto Scaling Instances (for e.g. EC2 based)

- To confirm whether the Database server quietly changed since last invocation (in the current session)

PSQL only - :HOST variable

When connected via PSQL, the HOST variable can tell which server you're currently connected to.  

postgres10@postgres=> SELECT :'HOST' db;

                            db

----------------------------------------------------------

 postgres10.xyz.ap-southeast-2.rds.amazonaws.com

(1 row)

Caveats:

• This variable can be set during session, so probably it is a good idea to use it only in trusted environments.
• This would need good testing with Proxy based architectures, since the value returned is what the client 'thinks' it connected to.
• Also note that if the client was connected via IP address, this method doesn't do a reverse DNS lookup, so what you'd get is the IP address.

→ psql -U postgres10 -h 172.31.45.165

psql (15devel, server 10.13)

Type "help" for help.

postgres10@postgres=> SELECT :'HOST' db;

      db

---------------

 172.31.45.165

(1 row)

PSQL only - \conninfo

Similar to the method above, another psql only method is to get Connection Info via the \conninfo command. This is more descriptive (and so may be not very well suited for some needs) but additionally doesn't work very well with SELECTs.

postgres10@postgres=> \conninfo

You are connected to database "postgres" as user "postgres10" on host "postgres10.xyz.ap-southeast-2.rds.amazonaws.com" (address "1.2.3.4") at port "54321".

RDS Only - Lambda invocation

Another way to get the Server Endpoint is to use a Lambda function and let that return the Server endpoint. You can read more about how to use Lambda with RDS servers here.

Aurora Postgres only - aurora_replica_status() function

When connected to Aurora Postgres, one additional way for a client to know which of the servers it is connected to (in the cluster), it can use the Aurora function aurora_replica_status() that tells which server you're currently connected to. One additional benefit with this mechanism is that it is executed on the server, and thus returns a true picture of the servers present (and their role) in the cluster.

apg2@pg=> select server_id from aurora_replica_status();

server_id
-----------
apg2b
apg2a
(2 rows)

Update: RDS Prewarm script updated to fetch FSM / VM chunks

(This post is in continuation to my previous post regarding Initializing RDS Postgres Instance)

This simple SQL "Initializes" the EBS volume linked to an RDS Instance, something which isn't possible to do without sending workload (and experience high Latency in the first run).

Key scenarios, where this is really helpful are:

• Create a Read-Replica (or Hot Standby in Postgres terms)
• Restore a new RDS Instance from a Snapshot

Update: The Script, now also does the following:

• Now also fetches disk blocks related to FSM / VM of all tables
• Now fetches all Indexes

Limitations that still exist:

• TOAST tables are still directly inaccessible in RDS
• Indexes for TOAST columns also fall under this category
• Trying hard to see if this last hurdle can be worked around
• Anyone with any ideas?!
• Script needs to be run once per Database Owner
• Not sure if there is any magic around this
• Object ownership is a Postgres property
• RDS Postgres does not give Superuser access
• I'll try to ease this in the future
• By creating a script to list the Users that this needs to run as
• The other possibility is to use DBLink to run this for separate Users in a single run

I'll update here, in case I make any significant changes.

Sample Run

-[ RECORD 1 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:40:08.291891-05

table_size          | 13 GB

freespace_map_size  | 3240 kB

visibility_map_size | 408 kB

blocks_prefetched   | 1639801

current_database    | pgbench

schema_name         | public

table_name          | pgbench_accounts

-[ RECORD 2 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:43:37.703711-05

table_size          | 2142 MB

freespace_map_size  | 0 bytes

visibility_map_size | 0 bytes

blocks_prefetched   | 274194

current_database    | pgbench

schema_name         | public

table_name          | pgbench_accounts_pkey

-[ RECORD 3 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:44:12.899115-05

table_size          | 440 kB

freespace_map_size  | 24 kB

visibility_map_size | 8192 bytes

blocks_prefetched   | 59

current_database    | pgbench

schema_name         | public

table_name          | pgbench_tellers

-[ RECORD 4 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:44:12.901088-05

table_size          | 240 kB

freespace_map_size  | 0 bytes

visibility_map_size | 0 bytes

blocks_prefetched   | 30

current_database    | pgbench

schema_name         | public

table_name          | pgbench_tellers_pkey

-[ RECORD 5 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:44:12.905107-05

table_size          | 40 kB

freespace_map_size  | 0 bytes

visibility_map_size | 0 bytes

blocks_prefetched   | 5

current_database    | pgbench

schema_name         | public

table_name          | pgbench_branches_pkey

-[ RECORD 6 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:44:12.907089-05

table_size          | 40 kB

freespace_map_size  | 24 kB

visibility_map_size | 8192 bytes

blocks_prefetched   | 9

current_database    | pgbench

schema_name         | public

table_name          | pgbench_branches

-[ RECORD 7 ]-------+------------------------------

clock_timestamp     | 2017-11-19 15:44:12.907142-05

table_size          | 0 bytes

freespace_map_size  | 0 bytes

visibility_map_size | 0 bytes

blocks_prefetched   | 0

current_database    | pgbench

schema_name         | public

table_name          | pgbench_history

Prewarming / Initializing an RDS Postgres instance (from S3)

UPDATE: Read this for recent updates. Now the SQL successfully fetches *all* disk blocks on most RDS PostgreSQL (read post for the rare exceptions).


As many of you know, that AWS RDS Postgres uses EBS which has an interesting feature called Lazy Loading that allows it to instantiate a disk (the size of which can be mostly anything from 10GB to 6TB) and it comes online within a matter of minutes. Although a fantastic feature, this however, can lead to unexpected outcomes when high-end production load is thrown at a newly launched RDS Postgres instance immediately after Restoring from a Snapshot.

One possible solution is to use the pg_prewarm Postgres Extension that is well supported in RDS Postgres, immediately after Restoring from a Snapshot, thereby reducing the side-effects of Lazy Loading.

Although pg_prewarm was originally meant for populating buffer-cache, this extension (in this specific use-case) is heaven-sent to initialize (fetch), (almost) the entire snapshot from S3 on to the RDS EBS volume in question. Therefore, even if you use pg_prewarm to run through all tables etc., thereby effectively evicting the recent run for the previous table from buffer-cache, it still does the job of initializing all disk-blocks with respect to the EBS volume.

I've just checked in the SQL to this repository that seems to do this magic pretty well. It also enlists why this would only take you ~70% of the way owing to restrictions / limitations (as per my current understanding).

In the Sample below, I restored a new RDS Postgres instance from a Snapshot and immediately thereafter ran this SQL on it.

• Notice that the first table (pgbench_accounts) takes about 22 seconds to load the first time, and less than a second to load the second time.
• Similarly the second table (pgbench_history) takes 15 seconds to load the first time and less than a second, the second time :) !

pgbench=>       SELECT clock_timestamp(), pg_prewarm(c.oid::regclass),
pgbench->       relkind, c.relname
pgbench->       FROM pg_class c
pgbench->         JOIN pg_namespace n
pgbench->           ON n.oid = c.relnamespace
pgbench->         JOIN pg_user u
pgbench->           ON u.usesysid = c.relowner
pgbench->       WHERE u.usename NOT IN ('rdsadmin', 'rdsrepladmin', ' pg_signal_backend', 'rds_superuser', 'rds_replication')
pgbench->       ORDER BY c.relpages DESC;
        clock_timestamp        | pg_prewarm | relkind |        relname
-------------------------------+------------+---------+-----------------------
 2017-11-07 11:41:44.341724+00 |      17903 | r       | pgbench_accounts
 2017-11-07 11:42:06.059177+00 |       6518 | r       | pgbench_history
 2017-11-07 11:42:17.126768+00 |       2745 | i       | pgbench_accounts_pkey
 2017-11-07 11:42:21.406054+00 |         45 | r       | pgbench_tellers
 2017-11-07 11:42:21.645859+00 |         24 | r       | pgbench_branches
 2017-11-07 11:42:21.757086+00 |          2 | i       | pgbench_branches_pkey
 2017-11-07 11:42:21.757653+00 |          2 | i       | pgbench_tellers_pkey
(7 rows)

pgbench=>
pgbench=>       SELECT clock_timestamp(), pg_prewarm(c.oid::regclass),
pgbench->       relkind, c.relname
pgbench->       FROM pg_class c
pgbench->         JOIN pg_namespace n
pgbench->           ON n.oid = c.relnamespace
pgbench->         JOIN pg_user u
pgbench->           ON u.usesysid = c.relowner
pgbench->       WHERE u.usename NOT IN ('rdsadmin', 'rdsrepladmin', ' pg_signal_backend', 'rds_superuser', 'rds_replication')
pgbench->       ORDER BY c.relpages DESC;
        clock_timestamp        | pg_prewarm | relkind |        relname
-------------------------------+------------+---------+-----------------------
 2017-11-07 11:42:33.914195+00 |      17903 | r       | pgbench_accounts
 2017-11-07 11:42:33.917725+00 |       6518 | r       | pgbench_history
 2017-11-07 11:42:33.918919+00 |       2745 | i       | pgbench_accounts_pkey
 2017-11-07 11:42:33.919412+00 |         45 | r       | pgbench_tellers
 2017-11-07 11:42:33.919427+00 |         24 | r       | pgbench_branches
 2017-11-07 11:42:33.919438+00 |          2 | i       | pgbench_branches_pkey
 2017-11-07 11:42:33.919443+00 |          2 | i       | pgbench_tellers_pkey
(7 rows)

psql \d now supports Interleaved / Compound SORTKEYs (in Redshift)

In continuation of support for Redshift series, now Describe Table (for e.g. \d tbl) shows SORTKEY details. This resolves Issue #6 and shows both COMPOUND / INTERLEAVED variations along with all the column names.

This change was complicated because Redshift doesn't natively support LISTAGG() function on System / Catalog tables, which meant that I had to resort to a pretty verbose workaround. This in-turn meant that this patch shows only the first ten COMPOUND SORTKEYs of a table. Seriously speaking, it would really take an extreme corner-case, for someone to genuinely require a SORTKEY with 10+ columns.

This is not a limitation for INTERLEAVED SORTKEY since it only supports a maximum of 8 Columns.


db=# CREATE TABLE tbl_pk(custkey SMALLINT PRIMARY KEY);
CREATE TABLE
db=# \d tbl_pk
                                           Table "public.tbl_pk"
 Column  |   Type   | Encoding | DistKey | SortKey | Preload | Encryption | Collation | Nullable | Default
---------+----------+----------+---------+---------+---------+------------+-----------+----------+---------
 custkey | smallint | lzo      | f       | 0       | f       | none       |           | not null |
Indexes:
 PRIMARY KEY, btree (custkey)

db=# CREATE TABLE tbl_compound(
db(#   custkey   SMALLINT                ENCODE delta NOT NULL,
db(#   custname  INTEGER DEFAULT 10      ENCODE raw NULL,
db(#   gender    BOOLEAN                 ENCODE RAW,
db(#   address   CHAR(5)                 ENCODE LZO,
db(#   city      BIGINT identity(0, 1)   ENCODE DELTA,
db(#   state     DOUBLE PRECISION        ENCODE Runlength,
db(#   zipcode   REAL,
db(#   tempdel1  DECIMAL                 ENCODE Mostly16,
db(#   tempdel2  BIGINT                  ENCODE Mostly32,
db(#   tempdel3  DATE                    ENCODE DELTA32k,
db(#   tempdel4  TIMESTAMP               ENCODE Runlength,
db(#   tempdel5  TIMESTAMPTZ             ENCODE DELTA,
db(#   tempdel6  VARCHAR(MAX)            ENCODE text32k,
db(#   start_date VARCHAR(10)            ENCODE TEXT255
db(# )
db-# DISTSTYLE KEY
db-# DISTKEY (custname)
db-# COMPOUND SORTKEY (custkey, custname, gender, address, city, state, zipcode, tempdel1, tempdel2, tempdel3, tempdel4, tempdel5, start_date);
CREATE TABLE
db=#
db=# \d tbl_compound
                                                                 Table "public.tbl_compound"
   Column   |            Type             | Encoding  | DistKey | SortKey | Preload | Encryption | Collation | Nullable |              Default
------------+-----------------------------+-----------+---------+---------+---------+------------+-----------+----------+------------------------------------
 custkey    | smallint                    | delta     | f       | 1       | f       | none       |           | not null |
 custname   | integer                     | none      | t       | 2       | f       | none       |           |          | 10
 gender     | boolean                     | none      | f       | 3       | f       | none       |           |          |
 address    | character(5)                | lzo       | f       | 4       | f       | none       |           |          |
 city       | bigint                      | delta     | f       | 5       | f       | none       |           |          | "identity"(494055, 4, '0,1'::text)
 state      | double precision            | runlength | f       | 6       | f       | none       |           |          |
 zipcode    | real                        | none      | f       | 7       | f       | none       |           |          |
 tempdel1   | numeric(18,0)               | mostly16  | f       | 8       | f       | none       |           |          |
 tempdel2   | bigint                      | mostly32  | f       | 9       | f       | none       |           |          |
 tempdel3   | date                        | delta32k  | f       | 10      | f       | none       |           |          |
 tempdel4   | timestamp without time zone | runlength | f       | 11      | f       | none       |           |          |
 tempdel5   | timestamp with time zone    | delta     | f       | 12      | f       | none       |           |          |
 tempdel6   | character varying(65535)    | text32k   | f       | 0       | f       | none       |           |          |
 start_date | character varying(10)       | text255   | f       | 13      | f       | none       |           |          |
Indexes:
 COMPOUND SORTKEY (address,tempdel2,start_date,custkey,zipcode,tempdel4,city,state,tempdel3,custname)

db=# CREATE TABLE tbl_interleaved(custkey SMALLINT) INTERLEAVED SORTKEY (custkey);
CREATE TABLE
db=# \d tbl_interleaved
                                      Table "public.tbl_interleaved"
 Column  |   Type   | Encoding | DistKey | SortKey | Preload | Encryption | Collation | Nullable | Default
---------+----------+----------+---------+---------+---------+------------+-----------+----------+---------
 custkey | smallint | none     | f       | 1       | f       | none       |           |          |
Indexes:
 INTERLEAVED SORTKEY (custkey)

As a side-note, there is a consideration as to whether this should be on a separate section of its own (and not under Indexes, which it clearly isn't). May be another day. Happy Redshifting :) !

Update (15th Sep 2017):
This project has now been named PsqlForks!

psql \d now supports DISTKEY / SORTKEY / ENCODING (in Redshift)

This is in continuation of my work for (my forked version of) psql to better support Redshift (read more here).

Now \d table provides some additional Redshift specific table properties such as:

• DISTKEY
• SORTKEY
• COMPRESSION (ENCODING)
• ENCRYPTION

Sample:

t3=# CREATE TABLE customer(

  custkey   SMALLINT                ENCODE delta NOT NULL,

  custname  INTEGER DEFAULT 10      ENCODE raw NULL,

  gender    BOOLEAN                 ENCODE RAW,

  address   CHAR(5)                 ENCODE LZO,

  city      BIGINT identity(0, 1)   ENCODE DELTA,

  state     DOUBLE PRECISION        ENCODE Runlength,

  zipcode   REAL,

  tempdel1  DECIMAL                 ENCODE Mostly16,

  tempdel2  BIGINT                  ENCODE Mostly32,

  tempdel3  DATE                    ENCODE DELTA32k,

  tempdel4  TIMESTAMP               ENCODE Runlength,

  tempdel5  TIMESTAMPTZ             ENCODE DELTA,

  tempdel6  VARCHAR(MAX)            ENCODE text32k,

  start_date VARCHAR(10)            ENCODE TEXT255

)

DISTSTYLE KEY

DISTKEY (custname)

INTERLEAVED SORTKEY (custkey, custname);

CREATE TABLE

t3=# \d customer

                                                                   TABLE "public.customer"

   Column   |            Type             | Encoding  | DistKey | SortKey | Preload | Encryption | Collation | Nullable |              Default

------------+-----------------------------+-----------+---------+---------+---------+------------+-----------+----------+------------------------------------

 custkey    | smallint                    | delta     | f       | 1       | f       | none       |           | not null |

 custname   | integer                     | none      | t       | 2       | f       | none       |           |          | 10

 gender     | boolean                     | none      | f       | 0       | f       | none       |           |          |

 address    | character(5)                | lzo       | f       | 0       | f       | none       |           |          |

 city       | bigint                      | delta     | f       | 0       | f       | none       |           |          | "identity"(493983, 4, '0,1'::text)

 state      | double precision            | runlength | f       | 0       | f       | none       |           |          |

 zipcode    | real                        | none      | f       | 0       | f       | none       |           |          |

 tempdel1   | numeric(18,0)               | mostly16  | f       | 0       | f       | none       |           |          |

 tempdel2   | bigint                      | mostly32  | f       | 0       | f       | none       |           |          |

 tempdel3   | date                        | delta32k  | f       | 0       | f       | none       |           |          |

 tempdel4   | timestamp without time zone | runlength | f       | 0       | f       | none       |           |          |

 tempdel5   | timestamp with time zone    | delta     | f       | 0       | f       | none       |           |          |

 tempdel6   | character varying(65535)    | text32k   | f       | 0       | f       | none       |           |          |

 start_date | character varying(10)       | text255   | f       | 0       | f       | none       |           |          |

Now that a few 'ToDos' are listed on Github Issues, next would probably involve working on this ticket, which aims at elaborate SORTKEY details (such as INTERLEAVED / COMPOUND) etc. when using Describe Table.

Update (15th Sep 2017):
This project has now been named PsqlForks!

Redshift support for psql

Am sure you know that psql doesn't go out of it's way to support Postgres' forks natively. I obviously understand the reasoning, which allowed me to find a gap that I could fill here.

The existing features (in psql) that work with any Postgres fork (like Redshift) are entirely because it is a fork of Postgres. Since I use psql heavily at work, last week I decided to begin maintaining a Postgres fork that better supports (Postgres forks, but initially) Redshift. As always, unless explicitly mentioned, this is entirely an unofficial effort.

The 'redshift' branch of this Postgres code-base, is aimed at supporting Redshift in many ways:

• Support Redshift related artifacts
• Redshift specific SQL Commands / variations
• Redshift Libraries
• Support AWS specific artifacts
• For e.g. AWS Regions
• Support Redshift specific changes
• For e.g. "/d table" etc.

The idea is:

• Maintain this branch for the long-term
• At least as long as I have an accessible Redshift cluster
• Down the line look at whether other Postgres forks (for e.g. RDS Postgres) need such special attention
• Although nothing much stands out yet
• Except some rare exceptions like this or this, which do need to go through an arduous long wait / process of refinement.
• Change the default port to 5439 (or whatever the flavour supports)
• ...with an evil grin ;)
• Additionally, as far as possible:
• Keep submitting Postgres related patches back to Postgres master
• Keep this branch up to date with Postgres master

Update (31st August 2017)

• Currently this branch supports most Redshift specific SQL commands such as
• CREATE LIBRARY
• CREATE TABLE (DISTKEY / DISTSTYLE / ...)
• Returns non-SQL items like
• ENCODINGs (a.k.a. Compressions like ZSTD / LZO etc )
• REGIONs (for e.g. US-EAST-1 etc.)
• Of course some complex variants (for e.g. GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA TO GROUP xxx ) don't automatically come up with tab-complete feature. This is primarily because psql's tab-complete feature isn't very powerful to cater to all such scenarios which in turn is because psql's auto-complete isn't a full-fledged parser to begin with.
• In a nutshell, this branch is now in a pretty good shape to auto-complete the most common Redshift specific SQL Syntax.
• The best part is that this still merges perfectly with Postgres mainline!

Let me know if you find anything that needs inclusion, or if I missed something.

====================================

$  psql -U redshift_user -h localhost -E -p 5439 db
psql (client-version:11devel, server-version:8.0.2, engine:redshift)
Type "help" for help.

db=#

Patch: Using --no-comments with pg_dump

Recently I submitted a patch for review that allows a non-superuser to practically use a backup taken from pg_dump.

Currently it is a kludge (and well known at that - Ref 1 / Ref 2 / Ref 3 / Ref 4) but since it's (the ideal solution) too big a byte to chew and not in high-demand, it has seen little to no traction in the past decade.

This patch should allow the above. But more importantly, it should also allow regular users of AWS RDS Postgres as well as Google Cloud Postgres databases (who do not get SuperUser access by-design) to reliably use the backups, instead of tinkering with the backup SQLs and remove things like COMMENT ON EXTENSION for it to even run during restoration.

The bad news is that since Postgres 10 has already branched off, I doubt this would see the light of the day (if at all) any time before Postgres 11 (unless there is consensus that it's helpful enough and gets 'back-patched' to Postgres 10 to be released around September 2017).

Update (3rd Oct 2017):
This is now a part of my PsqlForks branch. You can check the related commit here.

Update (26th Jan 2018):
This is now part of the official Postgres v11 branch. You can check the related commit here.

Using pg_dumpall with Google Cloud Postgres

Luckily the recent update to pg_dumpall works (without any extra effort) to dump Globals off the recently launched Google Cloud PostgreSQL service well.

This is because this Postgres service seems to be using the same limitation (as AWS RDS Postgres does) in that none of the user accounts are SuperUser in the Postgres sense. Therefore, this platform too disallows accessing pg_authid.

The fallback here too, is to use pg_roles to read (as much as is possible) to dump Role related Globals. Read here for more info on the recent changes to pg_dumpall that makes this work.

Using pg_dumpall with AWS RDS Postgres

My patch (allowing a non-super-user to dump Globals) just got committed to Postgres 10.0.

Besides the use mentioned above, this patch allows one to dump Globals from their AWS RDS Postgres Database and have a complete backup as a part of their Disaster Recovery (DR) solution. This earlier required an inelegant hack (detailed here in another post of mine) that wasn't very convenient for a regular user.

For those interested, RDS Postgres (by design) doesn't allow you to read pg_authid, which was earlier necessary for pg_dumpall to work. With this patch checked-in, pg_dumpall now uses a workaround (pg_roles) to read the same data (except passwords) and generate the same SQL Script.

With that mentioned, let's get our hands dirty and see a working solution:

# Take RDS Postgres Dump
# We store the pgdb database in pgdb.sql and globals in pgdb_globals.sql
pg_dumpall --globals-only --no-role-passwords \
  -U rdssuperuser -h rdsinstance \
  2>stderr.txt > pgdb_globals.sql
pg_dump -U rdssuperuser -h rdsinstance pgdb 2>stderr.txt > pgdb.sql

# Restore on Local Machine
psql -U postgres -c "CREATE DATABASE pgdb;"

# rdsadmin database (even if empty) is required for the next script
psql -U postgres -c "CREATE DATABASE rdsadmin;" 

psql -U postgres pgdb < pgdb_globals.sql
psql -U postgres pgdb < pgdb.sql

Note: The --no-role-passwords flag is still necessary since AWS RDS doesn't allow you to read Passwords. Therefore, the above generates a script that resets all users to be password-less, but that's still better than having to restore Globals (like 'CREATE USER ... ') manually by hand!

Big thanks to Simon for ironing out a few issues, Stephen, David & Robert for their (critical) reviews.

Custom pg_dumpall now works with AWS Redshift

While trying to work with AWS Redshift, it was interesting to see pg_dumpall failing to dump databases in my cluster! 

Delving further, for obvious reasons a managed service like this hides some global-information, which pg_dump(all) needs ... and no, this post is NOT about circumventing that.

This branch, gives a 'near' workaround for those who are okay with being able to extract all databases + (almost*) all Global information (Users etc.) in a single command. 

There are caveats though:

• Redshift doesn't support COPY TO, so the best workaround is using INSERT. Painful, but works.
• Barring passwords, all Globals can be dumped. The script just resets all users to be password-less, but that's better than having to do 'CREATE USER ... ' commands for your users by hand!

For some people, these caveats are going to be okay, considering that they get a scriptable way of taking a dump of all databases at one go, along with User information. 

If you're interested in a similar hack for AWS Postgres, you're in luck!

Down the line, I'll try to push this to the core, but for now, this works!

Go Play :) !!

Patched pg_dumpall works with AWS RDS Postgres

UPDATE: This patch is now a part of Native Postgres!! Read more here.

While trying to work with AWS RDS Postgres, it was interesting to see pg_dumpall failing to dump databases at all!

Delving further, for obvious reasons a managed service like this hides some global-information, which pg_dump(all) needs ... and no, this post is NOT about circumventing that.

This branch, gives a 'near' workaround for those who are okay with being able to extract all databases + (almost*) all Global information (Users etc.) in a single command. The *only* Global that can't be dumped are the User-Passwords, which for some people is okay, considering that they still get a scriptable way of taking a dump of all databases at one go, along with all User information. 

Note: For those keen to know, the script just resets all users to be password-less, but that's better than having to do 'CREATE USER ... ' commands for your users by hand!

Down the line, I'll try to push this to the core, but for now, this works!

UPDATE: This patch is now a part of Native Postgres!! Read more here.

Go Play :) !!