Category: database

Postgres pg_dump without password

August 26, 2022 / abhijit

I have to do a dump of a Postgres database on a regular basis.

So pg_dump is the obvious choice to do this as is cron to do this periodically.

But the main problem with this is – pg_dump expects a password to be entered at the prompt. There’s no way to pass the password in the prompt to be able to do this.

Stackoverflow to the rescue.

What is required is a .pgpass file in the home directory.

Connect to server running on host from inside a Docker container

August 4, 2022 / abhijit

After installing the CloudBeaver as a Docker instance, I was trying to connect to the Postgres instance which I was running on my local machine.

In the HOST field, I tried with localhost which obviously wouldn’t work since that refers to the VM itself.

After some searching I found that to connect to the host, I need to set the connection as

host.docker.internal

increase Postgres statement_timeout

May 6, 2022 / abhijit

Sometimes it is required to increase the statement_timeout for Postgres.

The correct way to do this would be to create a .psqlrc in the home directory and add the setting inside there.

$> cat .psqrc
set statement_timeout to 10000;

This is given in milliseconds so the above is for 10 seconds.

This can be verified from within psql command

psql> show statement_timeout;
 statement_timeout
-------------------
 10s
(1 row)

Reference: https://stackoverflow.com/a/24093305/33581

Export SQLite table to CSV

January 19, 2022 / abhijit

A number of times I’ve needed to export a specific table from an SQLite database to a CSV file.

The sqlite program itself gives a very clean interface for this.

sqlite3 -header -csv database.sqlite "SELECT * FROM artists;" > ./artists.csv

Conversely, if I need do this from the SQLite prompt, I need to do it slightly differently.

Postgres DB migration error due to replication

November 26, 2021 / abhijit

We got a weird error today when running the latest migration on a production Postgres DB.

cannot update table "schema_info" because it does not have a replica identity and publishes updates

The table schema_info is created when we run ruby Sequel migrations. This table stores just the version number of the latest migration applied under a column called version.

We had set up a replica database some time ago and it looked like the issue was because of this.

Postgres authentication

November 1, 2021 / abhijit

Reference: Stackoverflow

pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same username in the operating system as in PostgreSQL, use peer (UNIX, only for local connections) or sspi (Windows).

If there’s a password set, but pg_hba.conf doesn’t tell PostgreSQL to ask for it, the password is ignored.

If pg_hba.conf tells PostgreSQL to ask for a password but there’s none set, then all login attempts will fail no matter what password is supplied.

Create Postgres replica on separate server over the network

October 30, 2021 / abhijit

A database replica is able to provide another layer of protection in case of the main database server going down.

Postgres has this capability out of the box.

In this case the requirement was to create a replica on a separate server. (It’s also possible to create it on the same server where Postgres is running).

The replica maintains an exact copy of the main database (called Primary) on a real-time basis.

Run Postgres exposed to the internet

October 30, 2021 / abhijit

Although this is not recommended, there might be scenarios where there are no other options.

An example is an analysis software which doesn’t give the capability to connect via SSH. I came across this scenario when someone wanted to use QLik. Although this has a lot of capabilities, but when something like this is required, I’d rather use the open source and free Metabase.

Backup / Restore Postgres, MySQL, SQLite database using Sequel

October 27, 2021 / abhijit

Sequel is a fantastic ORM available in Ruby.

It also comes with an IRB console so it can be run directly from the command line with various options.

For example, to console into an SQLite database testdb.sqlite in the current directory –

$> sequel sqlite://testdb.sqlite

Or for Postgres –

$> sequel postgres://dbuser:dbpassword@localhost/testdb

However, I use sequel most frequently to take quick database backups or to restore them.

Setup up READ_ONLY users on Postgres

August 28, 2021 / abhijit

To grant access to users for only viewing a database (i.e. no INESRTs or CREATE or UPDATE access) there are certain conditions.

First, access should be only allowed through ssh.

Second, the ssh user should not be able to get into a shell directly.

And finally, the database should restrict access to that particular user with relevant permissions.