pg_dumpall with individual output files per database?

th lots of different scripting approaches. Many of those could simply be avoided with pg_dumpall supporting that already. Tools like BorgMatic making use of pg_dumpall might benefit of such a feature as well: https://projects.torsion.org/witten/borgmatic/issues/393 Thanks! Mit freundlichen Grüße

How much data does server side "loread" return?

t already and "fdRead" unnecessary? Thanks! Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: thorsten.schoen...@am-soft.de Web:http://www.AM-SoFT.de/ Te

Re: [SPAM] Re: How to hash a large amount of data within Postgres?

stead, like suggested? Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: thorsten.schoen...@am-soft.de Web:http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151-

How to hash a large amount of data within Postgres?

> hashHex := encode(hashBin, 'hex'); So, is there any way to work around the problem I have currently? Can I increase the memory restriction somewhere in the config? Are there any functions available working with blocks of data I'm missing now? I didn't find any state

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

gres.exe would wait until that process has finished, maybe locking the file to copy itself as well. Or "archive_timeout" interfering with some other operations or alike. Mit freundlichen Grüßen Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

r some reason seems more likely to me. > dwShareMode > FILE_SHARE_DELETE > Enables subsequent open operations on a file or device to request > delete access. Otherwise, other processes cannot open the file or > device if they request delete access. https://docs.microsoft.com/en-us/wind

Re: Which partition scheme makes sense for my time based IoT-datagrams?

e regarding monitoring IoT-devices, if they have been received at all and stuff like that. 15 months again is a value users are interested in, because they need one measuring value per month most likely. And 3 times 15 simply "looks" good as well... :-) Mit freundlichen Grüßen Thorsten S

Re: Which partition scheme makes sense for my time based IoT-datagrams?

indexing on the unpartitioned table to improve some of my example queries with not much luck. Mit freundlichen Grüßen Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK

Which partition scheme makes sense for my time based IoT-datagrams?

> AND "datagram"."captured_at" BETWEEN (CAST('2020-08-28T10:34:32.855+02:00' > AS TIMESTAMP WITH TIME ZONE) - CAST('P5D' AS INTERVAL)) AND > (CAST('2020-08-28T10:34:32.855+02:00' AS TIMESTAMP WITH TIME ZONE) + > CAST('P0D&#x

Re: Increased size of database dump even though LESS consumed storage

Guten Tag Thorsten Schöning, am Mittwoch, 10. Februar 2021 um 09:58 schrieben Sie: >> unpartitioned: 6,4 GiB >> half-yearly parts: 4,8 GiB >> yearly parts: 4,8 GiB The above number for `unpartitioned` might be wrong: I've re-created the same database, applied

Re: Increased size of database dump even though LESS consumed storage

ram_unpart; > CREATE INDEX idx_datagram_for_time_window ON datagram USING btree (src_meter, > captured_at DESC); Mit freundlichen Grüßen Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister

Re: Increased size of database dump even though LESS consumed storage

RTITION OF datagram FOR VALUES FROM > ('1970-01-01') TO ('1970-07-01'); > CREATE TABLE datagram_y1970_h2 PARTITION OF datagram FOR VALUES FROM > ('1970-07-01') TO ('1971-01-01'); > [...] > INSERT INTO datagram([...]) SELECT * FROM datagram_un

Re: Increased size of database dump even though LESS consumed storage

Guten Tag Thorsten Schöning, am Dienstag, 9. Februar 2021 um 10:56 schrieben Sie: > Any explanation for the increased dump size? Thanks! While I don't have a detailed explanation myself, there's the following hint [in the docs][1]: > For the custom archive format, this specifie

How does Postgres decide if to use additional workers?

width=39) (actual > time=9.883..86.390 rows=40044 loops=3) > Index Cond: (id = clt_rec.oms_rec) > Index Cond: ((captured_at >= ('2020-08-28 > 10:34:32.855+02'::timestamp with time zone - '5 days'::interval)) AND > (captured_

Re: Increased size of database dump even though LESS consumed storage

Guten Tag Thorsten Schöning, am Dienstag, 9. Februar 2021 um 10:56 schrieben Sie: > Any explanation for the increased dump size? Thanks! Some more data: > --compress=0: 20 vs. 25 GiB > --compress=DEFAULT: 6,5 vs. 5,6 GiB > --compress=9: 6,4 vs. 5,5 GiB From the docs

Increased size of database dump even though LESS consumed storage

timestamp with time zone NOT NULL, > captured_rssi smallint NOT NULL, > oms_statussmallint NOT NULL, > oms_enc bytea, > oms_dec bytea > ); Mit freundlichen Grüßen Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service -

Re: How to REMOVE a fillfactor set by accident?

Guten Tag Thomas Kellerer, am Donnerstag, 31. Dezember 2020 um 21:31 schrieben Sie: > ALTER INDEX pk_clt_rec_src RESET (fillfactor); Thanks, should have looked into the docs instead of googling only! :-) Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Serv

How to REMOVE a fillfactor set by accident?

't care at all instead? Because fillfactor=10 is only set for some low throughput test databases currently. Thanks! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH E-Mail: thorsten.schoen...@am-soft.de Web: http://www.AM-So

Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL

NTITY and that would need to be AVOIDED to NOT get multiple SEQUENCES? Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH E-Mail: thorsten.schoen...@am-soft.de Web: http://www.AM-SoFT.de/ Telefon: 05151- 9468-55 Fax: 05151-

Re: Declarative partitioning and automatically generated row-IDs using BIGSERIAL

27;s what I understood as well and is the reason why I asked: That means IDs could be duplicated manually within individual partition tables, while I need them to be unique across all of those. Additionally I wonder when IDs are generated by which SEQUENCE etc. Mit freundlichen Grüßen, Thors

Declarative partitioning and automatically generated row-IDs using BIGSERIAL

nship guaranteed in newer version when PRIMARY KEYS are still local to their individual partition table? Thanks for your help! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning AM-SoFT IT-Service - Bitstore Hameln GmbH E-Mail: thorsten.schoen...@am-soft.de Web: http://w

Re: What's your experience with using Postgres in IoT-contexts?

n received etc. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil..0178-8 9468- 04 AM-SoFT GmbH

Re: What's your experience with using Postgres in IoT-contexts?

ly, this doesn't seem worth it. And because telegrams are small, we would need some kind of container format anyway to not run out of inodes and stuff like that too easily. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT

Which is the setup with lowest resources you know Postgres is used in?

ms to better fit that use case than e.g. SQLite. Thanks for sharing you experiences and suggestions! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax..

Re: What's your experience with using Postgres in IoT-contexts?

e second like the latter. :-) If it's on the vehicle, I would be interested to somewhat know which hardware you use, to compare what I have in mind. Thanks! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme

Re: What's your experience with using Postgres in IoT-contexts?

r%20IoT.pdf > That is included as the standarDB in a vehicle tracking system we use, > although we have re[placed with PG. And your tracking system is more like the server that I already have or an embedded system within the vehicles themself? Mit freundlichen Grüßen, Thorsten Schön

What's your experience with using Postgres in IoT-contexts?

://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax

Re: How bad is using queries with thousands of values for operators IN or ANY?

and used etc. So for what query size or number of IDs to compare in IN would you consider a different approach at all? Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon

Re: How bad is using queries with thousands of values for operators IN or ANY?

it's worth or necessary to look into alternatives at all etc. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil..

Re: How to properly query lots of rows based on timestamps?

t only once instead? Which I guess it is? Is there any hint to such things in the plan? I didn't see or understood them. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon..

Re: How to properly query lots of rows based on timestamps?

width=12) (actual time=0.002..0.002 rows=1 loops=102799) > Index Cond: (id = clt_rec.oms_rec) Good news is that having one of those indexes in place at all makes a huge difference compared to having neither of both. :-D Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten S

Re: How to properly query lots of rows based on timestamps?

'll make a note and consider refactoring at some point, so thanks for your opinion! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax.

How to properly query lots of rows based on timestamps?

reducing things to dates or stuff like that to reduce the number of rows. > CREATE INDEX idx_clt_rec_captured_at ON clt_rec USING btree ( captured_at ); So where does the actual runtime come from in the above plan? Is it simply comparing lots of timestamps and that takes how long it takes? Or

Re: How to restore a dump containing CASTs into a database with a new user?

ings, especially if schemas are developed further over time etc. My current, already supported approach is far easier. I only need to take care about those CASTs manually now, nothing else yet. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@

Re: How to restore a dump containing CASTs into a database with a new user?

hing right from the start is the best one can do and that includes being able to reown in case of restoring backups. And Postgres supports most of that already, only the CAST-thing has not been thought to an end I guess. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning

Re: How to restore a dump containing CASTs into a database with a new user?

es". The error messages and docs say otherwise and changing the owner to a user which doesn't exist at all in the source-cluster doesn't make sense as well. When creating the dump, I can't know into which target database owned by which user it gets restored at some point. Mi

Re: How to restore a dump containing CASTs into a database with a new user?

r their individual databases as well. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil..0178-8 9468- 04

Re: How to restore a dump containing CASTs into a database with a new user?

easily. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil..0178-8 9468- 04 AM-SoFT GmbH IT-Syst

Re: How to restore a dump containing CASTs into a database with a new user?

Guten Tag Thorsten Schöning, am Sonntag, 19. Juli 2020 um 21:51 schrieben Sie: > If they are not only created by superusers, how can I restore CASTs to > a database owned by some other user? There are no other users than > the one owning the database in my case. I've retried things

Re: How to restore a dump containing CASTs into a database with a new user?

all other objects successfully restored to have a new owner, when CASTs seem to be per database as well. Looking at the source database, the CASTs in question are only shown for the database they are used in, no other. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: t

How to restore a dump containing CASTs into a database with a new user?

e public schema or one of the template databases or ...? What is the proper way to restore a dump containing arbitrary CASTs? I would have expected that pg_restore is handling everything, like it does for all other objects. Thanks! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schönin

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

Guten Tag Thorsten Schöning, am Montag, 8. Juni 2020 um 10:14 schrieben Sie: > When the table needs to be created, when is it visible to other > threads using the same transaction, before or after executing the > additional query? There is a misconception here: Multiple concurrent exe

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

stuff. i will think about that, so thanks for the hint! Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil.

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

implementation detail of some and others might allow truly concurrent access within one session? Nevertheless, I think I can at least partly answer some of my questions now. So thanks for forcing digging deeper into this than I obviously did before. :-) Mit freundlichen Grüßen, Thorsten

Re: Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

THIN one and the same session and transaction. :-) And that's where I wanted to make sure that I properly understood things using my questions. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http:

Concurrenctly running CREATE TEMP TABLE IF NOT EXISTS [...] AS [...]

that because of the same transaction used by multiple threads I need to synchronize them on web service-level? E.g. things like advisory locks won't work because they have session or transaction level and would be granted to all threads instantly. Thanks for your insights! Mit freund