Postgresql – How to install tds_fdw on AWS RDS for SQL Server

postgres has no password after an automated install, it's expected to authenticate through the peer method. sudo su - postgres is the first step to it. The second step is to connect through the Unix local domain socket, but your command doesn't do that, it connects through TCP, that's why you're stuck with the password problem.

The installed pg_hba.conf file probably starts with these two rules, apart from comments:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
[...]

When doing a TCP connection with -h localhost... the first rule doesn't match. The second rule matches and it triggers the demand for a password, but as none was set this can only fail (empty passwords are not allowed either).

The solution is to remove -h localhost from psql invocation, for the first rule to be taken instead. As it's on a Unix system, it will attempt a connection through the Unix domain socket.

Once logged to psql as the postgres user, you may set a password with the \password command or ALTER USER postgres PASSWORD 'foobar';

See also https://help.ubuntu.com/community/PostgreSQL
In the section Using pgAdmin III GUI , they suggest to change the pg_hba.conf first rule from auth peer to md5, after having set a password. Personally I don't quite get why. I'd rather leave that rule alone and run pgAdmin from my own Unix account, choosing localhost TCP connections.

It seems very surprising that you see an out of memory issue due to pg_stat_statements with the default kept statement count (5000). It makes somewhat more sense with a very high count, especially if you tried to query the view frequently.

One scenario, without going through the pgss code in detail, that I could imagine causing a memory issue, is the query text handling itself (not the statistics tracking), especially in the light of a lot of unique entries.

Given this is RDS we unfortunately can't check the size of the pgss file, but if you are open to trying this again (maybe you can reproduce a similar workload on a staging environment?), I think it would be useful to run a simple "SELECT * FROM pg_stat_statements" and visually look at the entries.

In particular I'd look for very large entries in the "query" field, as well as a lot of seemingly identical queries in pg_stat_statements (some ORMs tend to cause problems like this).

Also I'd recommend posting about this to the Postgres mailing lists - possibly to pgsql-bugs and/or pgsql-performance.

(For context: I built and operate pganalyze where we process a lot of pg_stat_statements data - memory issues like this are very rare)