Database

There are multiple reasons why someone wants to set up a postgres locally. Either for learning SQL or as an application’s backend. Over the years I see people struggle with using postgres locally, so here are common use cases and possible issues, with solutions for each. For Learning SQL SQL is very common for analysts to use for accessing data from a database, because the data size outgrows Excel. However, SQL is a query language, not a database engine. This essentially means if you want to get familiar with SQL, there are other simpler alternatives, such as SQLite or DuckDB (which can load data from local files directly without doing an explicit data import). Plus, you don’t need authentication to use either of them! ...

In certain cases, you can’t do a full postgres replication to another instance, or you prefer a fine-grained control on what to replicate, pglogical is one way to achieve partial replication, albeit this requires more manual setup. Below are steps I used to do a pglogical replication from AWS RDS to on-premise database. Note: If a subscriber (from the above example, the on-premise database) is offline, postgres WAL would balloon up. You’ll have to remove all traces of pglogical extension, including uninstalling pglogical extension, then reinitialize everything again to resolve the problem. ...