postgresql – Does daily pg_dump mess up postgres cache?

I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory.

Performances are way worse than on my previous provider, and new provider claims this is because the pg cache has to been “warmed up” everyday after automatic pg_dump backuping operations occuring in the night.

Geospatial queries that would normally take 50ms sometimes take 5-10s on first request, and some that would run in 800ms take minutes.

Is there something else looming or is the technical support right ?

If so, should I disable daily backups ? Or can I somehow use a utility function to restore the cache ? (pg_prewarm ?)

postgresql – Install postgres in VeraCrypt container

I would like to install postgres in a VeraCrypt container, to prevent saving data in plain text. During installtion process on the mounted container I got the error

"Problem running post-install step. Installation may not correctly. The database cluster initialisation failed."

After finishing the installation I couldn´t connect to the database via the command line psql (“postgres could not connect to server connection refused”).

I am using the windows installer on Windows 10.

How could I handle this problem?

postgresql – Prevent timing attacks in postgres?

I’m looking into accepting an API token from a user, and I would like to prevent timing attacks. The conventional wisdom seems to be that one should hash the token server-side in order to make the comparison non-deterministic. If you have some other identifier you can look up the record by that and then do a constant-time in-memory comparison, as with Rails secure_compare. In my case, though, I was planning on using the token to look up the user. I’m wondering if, by any chance, Postgres has some facility for doing constant-time comparisons when looking up records. Something that might look like:

SELECT * FROM users WHERE secure_compare(token, 'abcdef')

postgresql – Ltree query performance optimization of Postgres RDS DB

I have a AWS RDS m5.large Postgres 10.13 database that performs a lot of the following queries

SELECT "bundles".* FROM "bundles" WHERE "bundles"."version_id" = $1 AND (tree_path ~ ?) LIMIT $2

Here the table structure (roughly 1M rows):

CREATE TABLE "public"."bundles" (
    "id" int8 NOT NULL DEFAULT nextval('bundles_id_seq'::regclass),
    "cached_name" varchar NOT NULL,
    "uuid" uuid NOT NULL,
    "typology" int4 NOT NULL,
    "created_at" timestamp NOT NULL,
    "updated_at" timestamp NOT NULL,
    "resource_template_id" int8,
    "state" varchar,
    "resource_id" int8,
    "version_id" int4,
    "tree_path" ltree NOT NULL,
    "tree_ordinal" int4 NOT NULL DEFAULT 1,
    CONSTRAINT "fk_rails_a0e6c8e3c8" FOREIGN KEY ("resource_template_id") REFERENCES "public"."resource_templates"("id"),
    CONSTRAINT "fk_rails_02b50dac11" FOREIGN KEY ("resource_id") REFERENCES "public"."bundles"("id"),
    PRIMARY KEY ("id")
);

the problem is the poor performance of the overall system. Via advanced monitoring we see a very high value for current activity:

enter image description here

and it seems that the forementioned query have some sort of impact on the load by waits enter image description here

What do you suggest to check? I’m not a DBA so I can’t judge if those queries are efficent.

postgresql – determine maximum advisory locks supported by Postgres

According to the Postgres documentation, the maximum number of regular and advisory locks is limited by a shared memory pool:

Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.

How can I determine the size of this pool? Is this the same thing as shared buffers, which I can see with show shared_buffers; or is it something different? I am trying to determine roughly how many advisory locks my installation would be able to support because I am doing a ton of locking. My shared_buffers size is 5012MB.

I also have a couple more detailed questions:

  • If the server was unable to grant an advisory lock when I called pg_advisory_xact_lock(), would it hang, error out, or fail silently? As long as it doesn’t fail silently I’m good, although ideally it would hang and then continue once memory frees up.
  • I locking not only with advisory locks but also with SELECT ... FOR UPDATE. If I know the size of the pool, how can I calculate roughly how much space in the pool each advisory lock takes, and each SELECT ... FOR UPDATE takes? I know roughly how many rows will be impacted by each SELECT ... FOR UPDATE.

The documentation is a little confusing because if you look at the documentation for max_locks_per_transaction it says:

The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited.

This seems to track with the idea that the memory pool is equal to max_locks_per_transaction * max_connections described earlier, but here it is saying that the max has more to do with the number of tables and not the number of rows. I’m not really sure how to square this with the first quote, or how this relates to the space taken by advisory locks.

Any tips on calculating would be greatly appreciated!

postgresql – How to create support functions for gin index on custom operator class in postgres?

I created some custom operators for jsonb type and a class for them all. Problem is that when i create an index

CREATE INDEX idx_name on tableUSING gin(column_name custom_operator_class)

I get an error

missing support function 2 for attribute 1 of index "idx_name"

I probably need to create support classes for overlap, contains, containedBy and equal, but i am not finding any documentation on how to do that. All i found online is for btree, and nothing for gin. Does anybody know how to do this, or any material where i can find some examples?

If you need more information, i will be glad to say more. Operators are basically for recursive search of keys where date is less than, more than, equal to the specified one

java – Como realizar uma consulta com JPA a uma função do Postgres

Olá. Não consegui encontrar uma solução para o meu problema. No Postgres eu faço a consulta abaixo:

SELECT * FROM pgr_dijkstra('
SELECT gid AS id, source, target, length AS cost FROM ways',
11111, 22222, directed := false
) as di
  JOIN ways
ON di.edge = ways.gid
order by seq;

O “pgr_dijkstra” é uma função do POSTGIS, e não uma tabela. Me retorna o menor caminho entre o “source”(11111) e o “target(2222)”. O resultado é o JOIN entre os dados dessa função e a tabela “ways”. Além das colunas da tabela “ways”, temos 6 colunas que a pgr_dijkstra me retorna. Essas 6 colunas da função estão com o desenho de um cadeado na imagem abaixo, as colunas da direita são da tabela “ways”:

inserir a descrição da imagem aqui

Estou desenvolvendo uma aplicação em Spring Boot, onde o usuário me passa alguns dados, pego o valor do “source” e “target”, e tenho que chamar a “pgr_dijkstra” do banco de dados. Poderiam me ajudar dando o caminho das pedras de como posso fazer isso? Na minha pouca experiência com desenvolvimento sempre trabalhei com Spring Data, criando entidades que representavam tabelas, e interfaces Repository estendendo JPARepository. Terei que criar uma classe representando a tabela “ways” e anotá-la com @Entity? E os dados que toda essa função do dijkstra me retorna, como faço para representar eles no meu código como objeto para o JPA conseguir gerenciar e depois fazer o join? Sinceramente não sei como montar um repository, entidade e query para essa consulta envolvendo função Postgres. Não sei se nesse meu caso ainda é legal trabalhar com repositorys e objeto-relacional.

Obrigado pelo tempo de vocês lendo essa pergunta, quem puder me ajudar ficaria muito agradecido.

Is an AWS RDS postgres snapshot read consistent – the same as a consistent export in oracle? What happens to transactions in play?

When a snapshot is requested, do all transactions in play get held open (not committed) during the duration of the snapshot creation – only writing the previously committed data to the snapshot?

postgresql – Why is my Postgres database working for a while and then not able to “start server” once restarted?

Recently, I’ve started playing around with an old Raspberry Pi 3 b+, and I thought it would be good practice to host a Postgres database on my local network and use it for whatever I want to work through. I understand that running Postgres on a Raspberry Pi with 1GB of memory is not ideal and can take a toll on the SDcard, but I’ve updated the postgresql.conf file and specified that the data directory path is to utilize a 1TB SSD. Additionally, I’ve installed zram and log2ram to try and curb some of the overhead on SDcard.

Overview of tech I’m working with:

  • Raspberry Pi 4 B
  • Postgres 12
  • Ubuntu server 20.04 (no gui, only working from terminal)
  • 1TB SSD

Yesterday, I was writing to the Postgres db from a python notebook without any issue, but once I restarted the Raspberry Pi, I was unable to reach the db from DataGrip and would receive the following error from my terminal in Ubuntu:

psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I checked the status of the postgres server and that seemed to be alright…:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-01-28 13:34:41 UTC; 20min ago
    Process: 1895 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1895 (code=exited, status=0/SUCCESS)

Jan 28 13:34:41 ubuntu systemd(1): Starting PostgreSQL RDBMS...
Jan 28 13:34:41 ubuntu systemd(1): Finished PostgreSQL RDBMS.

This is what is provided in the postgresql-12-main.log:

2021-01-28 13:17:23.344 UTC (1889) LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-01-28 13:17:23.362 UTC (1889) LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-01-28 13:17:23.362 UTC (1889) LOG:  listening on IPv6 address "::", port 5432
2021-01-28 13:17:23.365 UTC (1889) LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-01-28 13:17:23.664 UTC (1899) LOG:  database system was shut down at 2021-01-28 01:43:38 UTC
2021-01-28 13:17:24.619 UTC (1899) LOG:  could not link file "pg_wal/xlogtemp.1899" to "pg_wal/000000010000000000000002": Operation not permitted
2021-01-28 13:17:24.670 UTC (1899) FATAL:  could not open file "pg_wal/000000010000000000000002": No such file or directory
2021-01-28 13:17:24.685 UTC (1889) LOG:  startup process (PID 1899) exited with exit code 1
2021-01-28 13:17:24.686 UTC (1889) LOG:  aborting startup due to startup process failure
2021-01-28 13:17:24.708 UTC (1889) LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

I think it could have something to do with permissions. If I try uploading a ~30MB csv to the postgres db, it will crash and the server will not start again because files cannot be written to the pg_wal directory. The only file in that directory is 000000010000000000000001 and does not move on to 000000010000000000000002 etc. while writing to a table.

Please let me know if you have any questions or if you would like for me to include any additional information. I appreciate any pointers you may have for head ahead of time.

postgresql – RDS Postgres chicken-and-egg problem altering roles

I just created an RDS Postgres instance, the user postgres (and it’s master password), and connected via psql as user postgres.

Then I ran the script generated from the current system using pg_dumpall --schema-only, and it immediately failed on the ALTER ROLE commands:

ERROR:  must be superuser to alter superusers

Ok, but how do I grant superuser to postgres? All of the documentation I’ve found “conveniently” skips that part…

EDIT: Here are the exact commands and error message:

postgres=> CREATE ROLE "READONLY";
CREATE ROLE
postgres=> ALTER ROLE "READONLY" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'mdXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
ERROR:  must be superuser to alter superusers