That Guy From Delhi: opensource

Showing posts with label opensource. Show all posts

29 Dec 2020

Which SQL causes a Table Rewrite in Postgres?

EDIT: Updated to v17 (devel) - (Jan 2024).

While developing SQL based applications, it is commonplace to stumble on these 2 questions:

What DDLs would block concurrent workload?
Whether a DDL is going to rewrite the table (and in some cases may need ~ 2x disk space)?

Although completely answering Question 1 is beyond the scope of this post, one of the important pieces that helps answering both of these questions is whether a DDL is going to cause a relfilenode change..

For a brief background, each regular table in Postgres stores data in one or more files, each of which is referenced in the postgres catalog with a relfilenode. A simple way to check whether the current implementation is going to create / refer to another copy (file) is whether the relfilenode changes. (TRUNCATE is a standout here, which by design is going to purge the table data, so although the relfilenode would change here, in total it obviously wouldn't consume anywhere close to 2x disk-space)

The table below shows which DDLs would cause a table rewrite. As has been discussed here, we need some more info to completely answer Question 1, however meanwhile this table helps in making some concurrency / disk-usage related decisions for all Postgres versions supported today.

18 Apr 2019

How about 1000 cascading Replicas :)

The other day, I remembered an old 9.0-era mail thread (when Streaming Replication had just launched) where someone had tried to daisy-chain Postgres Replicas and see how many (s)he could muster.

If I recall correctly, the OP could squeeze only ~120 or so, mostly because the Laptop memory gave way (and not really because of an engine limitation).

I couldn't find that post, but it was intriguing to know if we could reach (at least) a thousand mark and see what kind of "Replica Lag" would that entail; thus NReplicas.

On a (very) unscientific test, my 4-Core 16G machine can spin-up (create data folders and host processes for all) 1000 Replicas in ~8m (and tear them down in another ~2m). Now am sure this could get better, but amn't complaining since this was a breeze to setup (in that it just worked without much tinkering ... besides lowering shared_buffers).

For those interested, a single UPDATE on the master, could (nearly consistently) be seen on the last Replica in less than half a second, with top showing 65% CPU idle (and 2.5 on the 1-min CPU metric) during a ~30 minute test.

Put in simple terms, what this means is that the UPDATE change traveled from the Master to a Replica (lets call it Replica1) and then from Replica1 it cascaded the change on to Replica2 (and so on a 1000 times). The said row change can be seen on Replica1000 within half a second.

So although (I hope) this isn't a real-world use-case, I still am impressed that this is right out-of-the-box and still way under the 1 second mark.... certainly worthy of a small post :) !

Host: 16GB / 4 core

Time to spin up (1000k Cascading Replicas): 8minutes

Time to tear down: 2 minutes

Test type: Constant UPDATEs (AV settings default)

Test Duration: 30min

Time for UPDATE to propagate: 500 ms!! (on average)

CPU Utilization: ~65%

CPU 1-min ratio: 2.5

20 Nov 2017

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

7 Nov 2017

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)

14 Oct 2017

First alpha release of PsqlForks - Menon

Primer: PsqlForks aims to support all DB Engines that (even partially) speak Postgres (psqlforks = psql for Postgres forks).

Given that PsqlForks has been in development for a few weeks, it's time to stabilize a bit and towards that, we finally have Menon, PsqlForks first Alpha Release. Being an alpha, by definition it isn't ready for production, but it feels stable enough ... feel free to test it out!

Importantly, this fork is synced with postgres/master regularly, and should ideally sport all recent psql developments. Further, I am not a C expert and am just barely comprehending Postgres, so let me know of any 18-wheelers that I didn't see.

The release title - 'Menon', is a common sub-Caste in South-Indian state of Kerala. Selecting this nomenclature emanates from the idea of popularizing (heh!) common names and places from Kerala... and that it doesn't hurt to have an identifiable name (and while at it, add character) to a Release :)

This release includes:

Decent support for Redshift:

SQL tab completion for Redshift related variations
\d etc. now support Redshift specifics - ENCODINGs / SORTKEYs / DISTKEY / COMPRESSION etc.
Support Temporary Credentials using IAM Authentication (via AWS CLI)
View detailed progress here.

Basic support / Recognition semantics for:

CockroachDB - view progress here
PipelineDB
PgBouncer
RDS PostgreSQL

You could read more here:

For the interested:

Upcoming Milestones: https://github.com/robins/postgres/milestones
Existing open issues: https://github.com/robins/postgres/issues
Feedback / Bugs? Post them here

13 Oct 2017

PsqlForks supports AWS IAM authentication for Redshift

With this commit, PsqlForks ( http://psqlforks.com ) can now fetch credentials from AWS IAM. Read more about Redshift's support for generating database credentials using IAM authentication feature, here.

Since the entire AWS CLI isn't baked into PsqlForks (yet!), you'd need a working copy of AWS CLI installed / working on the host (from where psql is called).

This took a while, since I missed the basic assumption that Redshift enforces SSL and psql doesn't attempt SSLMODE by default in the first try. The fact that CYGWIN wasn't super-smooth with AWS CLI in my test installation, didn't help either.

But as they say, all's well that ends well. There are few obvious additions that are possible (such as expiration validation / re-use unexpired credentials on re-connect etc.) but this should get merged in the forks mainline soon.

I guess it's time to begin thinking of releases, instead of making the mainline jittery with feature additions such as this one.

Yenjoy!

$ psql "sslmode=require host=redshift_cluster port=5439 dbname=redshift2" -U testing1
Password for user testing1:
psql: fe_sendauth: no password supplied

$ psql -I "sslmode=require host=redshift_cluster port=5439 dbname=redshift2" -U testing1

CLI: aws redshift get-cluster-credentials --auto-create --db-user testing1 --cluster-identifier redshift2 # Informational / testing output

psql (client-version:11devel, server-version:8.0.2, engine:redshift)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.

redshift2=> select current_user;
current_user
--------------
testing1
(1 row)

redshift2=> \du
List of roles
Role name | Attributes | Member of
-----------+-------------------------------+-----------
redshift2 | Superuser, Create DB +|
| Password valid until infinity |
testing1 | |

redshift2=> \q

$ ./psql --help | grep -i iam
-I, --aws-iam-redshift use temporary database credentials from AWS IAM Service

12 Oct 2017

PsqlForks now recognizes RDS PostgreSQL separately

With this commit, PsqlForks ( http://psqlforks.com ) now recognizes RDS PostgreSQL separately.

This isn't utilized much yet, but the infrastructure is going to be helpful in skipping / avoiding some commands that are defunct / not possible in the RDS PostgreSQL offering.

29 Sept 2017

PsqlForks now recognizes PgBouncer

With this commit, PsqlForks knows when it's talking to PgBouncer (and not Postgres).

Down the line, this should pave way for PsqlForks to more cleanly convey why (most of) the given psql shortcut(s) don't work (and what else does).

As always, the psql/README always has the most updated status of any engine support.

$ psql -h localhost -E -p6543 -U postgres pgbouncer
psql (client-version:11devel, server-version:1.7.1/bouncer, engine:pgbouncer)
Type "help" for help.

pgbouncer=# show version;
NOTICE: pgbouncer version 1.7.1
SHOW
pgbouncer=#

25 Sept 2017

PsqlForks now supports CockroachDB

PsqlForks now supports CockroachDB as much as is currently possible. You can check it's current SQL status here.

$ /opt/postgres/master/bin/psql -h localhost -E -p 26257 -U root
psql (client-version:11devel, server-version:9.5.0, engine:cockroachdb)
Type "help" for help.

root=> select version();
version()
--------------------------------------------------------------------------
CockroachDB CCL v1.0.6 (linux amd64, built 2017/09/14 15:15:48, go1.8.3)
(1 row)
bank=> \l

List of databases

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

(5 rows)

bank=> \dv

List of relations

Schema | Name | Type | Owner

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

bank | a | view |

(1 row)

bank=> \di

List of relations

Schema | Name | Type | Owner | Table

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

bank | primary | index | | accounts

system | jobs_status_created_idx | index | | jobs

system | primary | index | | descriptor

system | primary | index | | eventlog

system | primary | index | | jobs

system | primary | index | | lease

system | primary | index | | namespace

system | primary | index | | rangelog

system | primary | index | | settings

system | primary | index | | ui

system | primary | index | | users

system | primary | index | | zones

(12 rows)

15 Sept 2017

PsqlForks now supports PipelineDB

After working on this PSQL variant that intends to support all Postgres forks, I finally narrowed down to naming it.

Since this was essentially Psql (for) Forks, quite intuitively, I chose to name it PsqlForks.

Considering that until recently this fork just supported Amazon Redshift, this naming didn't make much sense if it wasn't supporting at least 2 forks :) !

Thus, PsqlForks now supports PipelineDB!

$ /opt/postgres/master/bin/psql -U pipeline -p 5434 -h localhost pipeline
psql (client-version:11devel, server-version:9.5.3, engine:pipelinedb)
Type "help" for help.

pipeline=# \q

2 Sept 2017

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

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!

31 Aug 2017

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"

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

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

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

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

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!

12 Aug 2017

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=#

31 May 2017

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.

horntail it is :)

I had been planning to contribute to Postgres Buildfarm by running it on one of my VMs and finally got that done... and horntail it is.

A few things to note, before it fades from (my) memory:

It (obviously) requires quite a bunch of libraries, especially if you're using the defaults (for e.g. with ssl / krb5 etc..)

You may either disable some of those exotic options (not ideally recommended)
Or, you may use a silo'ed VM just for this purpose so that it doesn't slow your regular work
I did neither (of the above) but didn't care about performance of this VM

Probably would enable more Options / Branches down the line and re-submit

My distro was Ubuntu and so YMMV, but the packages weren't very obvious. If you see something fail and you check logs, you'd see that 'abc' was missing, but wouldn't have the faintest idea which package satisfied that

Some hiccup missing libraries were (These may be obvious for most, but were new to me ):

gss_init_sec_context
msgfmt
tclConfig

It'd be good if someone could catalog the packages required

For all Unix variants - obviously easier said that done, but

It's possible that there may be more people willing (like me) but unable to scale all the walls

At least the super-set of what's required when all flags are enabled, from which people could try what's needed for his / her set of enabled flags

As it always is, the documentation is relatively clear, just that persistence (especially for exotic Linux variants / issues) is needed in large quantities.

All the best :)

14 Mar 2017

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.

8 Mar 2017

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.

24 Dec 2016

Watch an Online Movie: (Wget -c || Deluge) && Chrome > Chromecast

For all those who are in an odd situation where:

They have a paid account to a Movie site (like Netflix etc.)

Are unable to watch movies online, just because the video-streaming is just too slow

Either because your convenient times are 'peak' times for the server
Or, you are behind a painfully bad ISP

And are able to download the movie, as an option.

To such customers, downloading the movie overnight (using for e.g. wget) would be a big help!

I regularly use this, to download the movie, and watch with non-tech people (my kids) who can't be explained why the movie keeps 'Buffering'!

c:\bin\wget \
-O KD1242.mp4 \ # Output filename
-t 0 \ # Retrying indefinitely
-c -T 10 \ # Reconnect + Timeouts are 10 seconds
-w 10 \ # wait 10 seconds before retrying a disconnection
"https://www.yourfavouritechannel.com/abcd/KD1242.mp4?g=1f3410635&sha1=JGgJm02BOvqgsdvC32BcUg"

Windows binaries for GPL'ed GNU software (such as Wget) are heaven sent here:

Wget: http://gnuwin32.sourceforge.net/packages/wget.htm

And if you need a Big-TV experience (for e.g. if you have a Chromecast), you could stitch things together by using the Google Cast extension for your Chrome Browser, and open up "c:\" on the browser to play the movie directly in the browser (since VLC stream is still in Beta):

ChromeCast Extension for your Chrome Browser: https://chrome.google.com/webstore/detail/google-cast/boadgeojelhgndaghljhdicfkmllpafd?hl=en

If by chance you're downloading videos via torrents (For e.g. NASA videos), here are my GPL recommendations for Windows:

Deluge: If you haven't seen this, you should really replace your uTorrent etc. clients with this one
Use the Streaming Extension (Github Link)
Copy the URL that the Extension provides + Paste to Chrome
Cast your tab to your Chromecast + Enjoy!

Have Fun!