Re: vacuum is time consuming
Ok Martin, I got ur ur point of max limit of maintenance_work_mem is 1 GB but there is nothing mentioned about the same in postgresql.conf as remarks for this specific parameter. Is there any other option to increase the speed of vacuum? Regards Atul On Tuesday, February 2, 2021, Martín Marqués wrote: > Hi Atul, > > > We have DB of 400GBs, Server RAM is 160GBs. CPU consumption is 1-3% > only. > > > > We have configured maintenance_work_mem to 10GBs and restarted the > > postgres service. > > Just wanted to mention that maintenance_work_mem has a hardcoded upper > limit threshold of 1GB, so any size bigger than that to > maintenance_work_mem or autovacuum_maintenance_work_mem will leave it > effectively at 1GB. > > There have been a few attempts the past few years on lifting that > restriction. > > > We have done upgrade from postgres 9.5 to 9.6, then when we run vacuum > > analyze on database, it is taking more than 5 hours and still running. > > > > Any suggestions for making the process(vacuum analyze) faster are > welcome. > > Yes, upgrade to PG13. > > Kind regards, Martin, > > > -- > Martín Marqués > It’s not that I have something to hide, > it’s that I have nothing I want you to see >
Re: vacuum is time consuming
On Tue, 2021-02-02 at 13:44 +0530, Atul Kumar wrote: > Is there any other option to increase the speed of vacuum? For autovacuum, decrease "autovacuum_vacuum_cost_delay". Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: count(*) vs count(id)
On Mon, 2021-02-01 at 19:14 -0600, Matt Zagrabelny wrote: > > > What is count(*) counting then? I thought it was rows. > > > > Yeah, but count(id) only counts rows where id isn't null. > > I guess I'm still not understanding it... > > I don't have any rows where id is null: Then the *result* of count(*) and count(id) will be the same. The asterisk in count(*) is misleading. Different from any other programming language that I know, the SQL standard has decided that you cannot have an aggregate function without arguments. You have to use the asterisk in that case. So count(*) really is count(), that is, it counts one for every row that it finds, no matter what the row contains. But count(id) includes a check: if "id IS NULL", it is not counted. If that condition is satisfied for all "id"s, you end up with the same count. But count(id) is more expensive, because it will perform this unnecessary NULLness check for each row. In short: use count(*) if you want to count rows, and use count(x) if you want to count all rows where x IS NOT NULL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: permission denied for large object 200936761
On Tue, 2021-02-02 at 00:20 +0200, Andrus wrote: > > Obviously large objects *are* used. > > How to figure out what is this large object ? You can extract it with \lo_export 200936761 'somefile' in psql and examine the file. Ask the people who use that database! Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: permission denied for large object 200936761
Hi! You can extract it with \lo_export 200936761 'somefile' in psql and examine the file. Ask the people who use that database! Tried root@c202-76:~# ./pgsqlkaiv.sh psql (12.2 (Debian 12.2-2.pgdg100+1)) Type "help" for help. sba=# \lo_export 200936761 'large200936761' lo_export sba=# \q root@c202-76:~# ls -l large* -rw-r--r-- 1 root root 0 veebr 2 10:45 large200936761 result file size is 0 . Andrus.
Re: permission denied for large object 200936761
Hi! >I have imported data from other clusters and executed lot of different sql commands. I have used grant, revoke, reassign commands to change privileges for other users and have deleted and added users. I don't suppose this was done in a structured way that could be gone back over? Exact command sequence cannot restored. I have script to normalize rights for user. It removes all rights first and set desired rights afterwards. This was executed lot of times, it is used for years. Also there were manual user rights adjustments using sql commands in cases there this universal script cannot used. There are approx. 300 postgres roles in cluster, users are changing in every week. Previous dumpuser backup which suceeds was at January 4th, 9 MB in custom format. There are nightly backups of databases is cluster. There is also hot standby, base backups in every sunday using pg_basebackup and WAL archiving. WAL archiving and hot standby was broken in previous week (I increased max_connections=400 in main server but forget to increase this in standby server, WAL archiving is also from hot standby server). You could try some of the functions here: https://www.postgresql.org/docs/12/lo-funcs.html to see if you can figure it out. There is only one function , lo_get() in this page which returns data. I tried Actually there is a second lo_export() at bottom of page. It needs superuser privilege and access to the server file system. Tried in server using psql select lo_export(200936761,'large1'); select lo_export(200936762,'large2'); select lo_export(200936767,'large3'); result files have zero size. >What happens if you query: https://www.postgresql.org/docs/12/catalog-pg-largeobject.html as a superuser? > Do you see anything in the data field? select * from pg_largeobject running as superuser returs empty table with 3 columns: loid, pageno and data Andrus.
Re: permission denied for large object 200936761
Hi! I don't suppose this was done in a structured way that could be gone back over? Accidently '200936767'::lo cast was issued : INSERT INTO report ( ... ) values (.. , '200936767'::lo, ... ) server throws error type "lo" does not exist for this. Maybe this causes orphan large object creation by server or by odbc driver. How to fix this ? report table shoud not have lo type columns. No idea why this cast is generated using psqlodbc Andrus.
Re: permission denied for large object 200936761
On Tue, 2021-02-02 at 10:48 +0200, Andrus wrote: > > You can extract it with > > \lo_export 200936761 'somefile' > > > > in psql and examine the file. > > > > Ask the people who use that database! > > Tried > > root@c202-76:~# ./pgsqlkaiv.sh > > psql (12.2 (Debian 12.2-2.pgdg100+1)) > Type "help" for help. > > sba=# \lo_export 200936761 'large200936761' > lo_export > sba=# \q > > root@c202-76:~# ls -l large* > -rw-r--r-- 1 root root 0 veebr 2 10:45 large200936761 > result file size is 0 . So? What is your point? Somebody created a large object of size 0. Don't ask me who did it, ask the people who use this database. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: libpq and mysterious "invalid byte sequence for encoding UTF8".
On 02.02.2021 3:53, Tom Lane wrote: > rob stone writes: >> Columns:- maincontact boolean DEFAULT false, >> publdatacontact boolean DEFAULT false, >> invcontact boolean DEFAULT false, >> queries_recipient boolean, >> fakelastname boolean NOT NULL DEFAULT false, >> are defined as booleans and all except one have defaults declared, yet >> you are passing NULL's which the insert tries to cast to boolean. >> Maybe this is the cause of the error?? > Oooh ... if we posit that the statement is expected to throw an error, > maybe what this error is complaining about is an inability to translate > a localized error message to the client's encoding. This could reflect > an incorrectly-encoded .po file, for example. So what we need to know > next is what lc_messages setting Jiří is using, and also the server > and client encodings. Still, this does not explain why it sometimes work and why the byte sequence in the error message changes on every run, no? I'm getting the error even when I changes the boolean fields from NULL to FALSE. Client and server encoding is UTF8. Locales are set to C.UTF8.
Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04
I'm using postgresql 11 builds from http://apt.postgresql.org/pub/repos/apt/
- I've got a database created under Ubuntu 18.04, and recently updated to
Ubuntu 20.04. These are all docker builds
I've got an index defined as follows
CREATE UNIQUE INDEX entity_settings_wh_unique_rawdata ON wrd.entity_settings
USING btree (attribute, digest(upper((unique_rawdata)::text),
'sha256'::text)) WHERE ((attribute IS NOT NULL) AND ((unique_rawdata)::text
<> ''::text)
And I've got a database which, when started under Ubuntu 18.04, finds one
record for this query:
SELECT * FROM "wrd"."entity_settings" T1 WHERE upper(left(T1."rawdata",
264))=upper(left('UT-MC', 264)) AND (T1."attribute"=3060);
But if I start it with Ubuntu 20.04, with the same postgres version, the
query doesn't find anything.
Switching back to postgres 11 under 18.04 shows the record again, back to 11
under 20.04, and it's gone. Somehow postgres 11 under 20.04 cannot read the
index created by 18.04
select version on 18.04 gives:
PostgreSQL 11.10 (Ubuntu 11.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
select version on 20.04 gives:
PostgreSQL 11.10 (Ubuntu 11.10-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
so as far as I can tell, these should be almost exactly the same. I can't
find any difference in the environment either, and "SHOW ALL" is identical
for both installations
Any pointers on what I should be looking at why these two installations
might view the same data differently?
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04
On Tue, Feb 2, 2021 at 11:20 AM unilynx wrote: > > I'm using postgresql 11 builds from http://apt.postgresql.org/pub/repos/apt/ > - I've got a database created under Ubuntu 18.04, and recently updated to > Ubuntu 20.04. These are all docker builds This is a known problem when upgrading Ubuntu (and most other Linux distributions, but it depends on which version of course -- btu for Ubuntu LTS the problem is triggered when going to 20.04). If you have any indexes on text-type columns, they need to be reindexed. See https://wiki.postgresql.org/wiki/Locale_data_changes Note that this is triggered by the Ubuntu upgrade, not by upgrading PostgreSQL -- that's why it happend even when you keep using the same PostgreSQL version. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: Index created with PGSQL11 on ubuntu 18.04 corrupt with PGSQL11 on ubuntu 20.04
Thanks, that was quick. Didn't realise the distribution's locale tables would have been a source of differences too -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
RE: Foreign table performance issue / PostgreSQK vs. ORACLE
Hi! My PostgreSQL version is 11.8. The query I am running is referring to a number of foreign tables. The first one (table1) has to IDs, let's say ID_A and ID_B. While ID_A is unique, ID_B is not. In my case, I am pulling formation for a value of IB_B for which about 800 rows (with unique ID_A) exist. I found: While select * from my_view where id_b='some value'; seemingly runs "forever" (I cancelled execution after a few hours), the following completes in about 1 hr: select * from my_view where ia_a in ( select id_a from table1 where id_b='some value' ); So, I tried smaller chunks of ID_a and found the execution time is non-linear with respect to number of IDs. For e.g. 50 ID_A's, it was completed in about 12 sec. Next I have split the ~800 ID_A's into chunks of 50 and submitted these 16 queries one after another. They all completed in about 12 secs, each. I then found the option fetch_size, e.g. ALTER SERVER some_server OPTIONS (fetch_size '5'). A chunk of 50 now executes in 2 seconds (instead of 12 before). So, I found the "size" of the query has a serious impact to the execution time. I don't really understand why execution 16*50 takes 16*2 secs only, but executing 1*800 takes about 3000 seconds... Regards, Ingolf -Original Message- From: Sebastian Dressler [mailto:sebast...@swarm64.com] Sent: 30 January 2021 11:45 To: Markhof, Ingolf Cc: pgsql-general@lists.postgresql.org Subject: [E] Re: Foreign table performance issue / PostgreSQK vs. ORACLE Hi Ingolf, > On 29. Jan 2021, at 13:56, Markhof, Ingolf > wrote: > > Hi! > > I am struggling with the slow performance when running queries referring to > foreign tables. – Yes, I know… - Please read the whole story! Done and it rings a bell or two. > The set-up basically is a production database and a reporting database. As > names indicate, the production database is used for production, the reporting > database is for analysis. On the reporting database, the only way to access > product data is via foreign tables that link to the related production tables. > > Now, while some queries on the reporting service run fine, some don't even > return any data after hours. > > However, the same set-up worked fine in Oracle before. Reporting wasn't > always fast, but it delivered results in acceptable time. A query executed on > the Oracle reporting server returns data in e.g. 30 seconds. But running the > query translated to PostgreSQL on the PostgreSQL DB does not deliver a single > row after hours (!) of run time. > > So, I wonder: Is there a fundamental difference between Oracle database links > and foreign tables in PostgreSQL that could explain the different run times? > Could there be some tuning option in PostgreSQL to make queries via foreign > tables faster (e.g. I heard about option fetch_size)? You did not explicitly mention it, but I assume you are using postgres_fdw to connect from reporting (R) to production (P). Thomas and Tom already mentioned incomplete/non-existing/non-applicable filter pushdowns. I want to add another probable root cause to the list explaining the behavior you experience. The postgres_fdw uses a CURSOR on P to execute the query. While this guarantees transaction safety, it also prohibits parallelism (PostgreSQL server-side cursors enforce a sequential plan). As a result, depending on the size of tables, indexes, and filters pushed down (or not), this probably results in slow-running queries. IMO, the worst-case scenario is that a sequential table scan without any filtering, and a single worker runs on the target. Of course, you can try to optimize schemas on P and queries on R, enabling more filter pushdown and eventually a faster execution. However, I believe this does not work with your entire workload, i.e. there will always be performance gaps. The parallelism issue is theoretically fixable by utilizing partitions on P. R then connects to P with multiple postgres_fdw-backed child tables. However, this will only work with a patch to postgres_fdw to implement "IsForeignScanParallelSafe" (see [1] for a possible implementation). Without this method, there will be no parallelism again. Without, the partitions scan occurs sequentially, not showing a performance gain. I want to mention there are proprietary options available (re-)enabling PostgreSQL parallelism with cursors. Such an extension can potentially fix your performance issue. However, I have not tried it so far with a setup similar to yours. Cheers, Sebastian [1]: https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_swarm64_parallel-2Dpostgres-2Dfdw-2Dpatch&d=DwIGaQ&c=udBTRvFvXC5Dhqg7UHpJlPps3mZ3LRxpb6__0PomBTQ&r=ivZWA-ECVj3XrXBe0obDwKY7Ui7K5Nj9oD2KKWLm0Bw&m=urVtRLfrc1kNan7AL2Al4g0Dq-bCi5UPxtnOEzHlj_U&s=ZkvPe7hWFG3H6Q2q9bca7l984-UxMeNw1fFOAyLWlPg&e= -- Sebastian Dressler, Solution Architect, Swarm64 AS +49 30 994 0496 72 | sebast...@swarm64
Re: permission denied for large object 200936761
Hi! So? What is your point? Somebody created a large object of size 0. report table has bytea column. It looks like psqlodbc driver adds ::lo cast when inserting binary data: https://github.com/hlinnaka/psqlodbc/blob/master/convert.c#L4564 and this adds row to pg_largeobject_metadata table. Why it adds cast to lo type ? This type does not exist in Postgres server and causes server error. Andrus.
Re: vacuum is time consuming
Hi, > > Is there any other option to increase the speed of vacuum? > > For autovacuum, decrease "autovacuum_vacuum_cost_delay". He mentioned in the original message that his problems was with a global VACUUM after upgrading, so cost_delay should be zero by default. Regards, -- Martín Marqués It’s not that I have something to hide, it’s that I have nothing I want you to see
Re: ransomware
Hi, I know its quite general. It is as I dont know what approaches may exist. Requirement is extremely simple: Is there anyway, from a running postgres standpoint, to be aware that a ransomware is currently crypting your data ? answer can be as simple as: when postgres do crash. something else ? Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Feb 2, 2021 at 2:37 AM Michael Paquier wrote: > On Mon, Feb 01, 2021 at 03:38:35PM +0100, Marc Millas wrote: > > there are various ways to do those checks but I was wandering if any > > ""standard''" solution exist within postgres ecosystem, or someone do > have > > any feedback on the topic. > > It seems to me that you should first write down on a sheet of paper a > list of all the requirements you are trying to satisfy. What you are > describing here is a rather general problem line, so nobody can help > without knowing what you are trying to achieve, precisely. > -- > Michael >
Re: ransomware
On 2021-02-02 15:44:31 +0100, Marc Millas wrote: > I know its quite general. It is as I dont know what approaches may exist. > > Requirement is extremely simple: Is there anyway, from a running postgres > standpoint, to be aware that a ransomware is currently crypting your data ? PostgreSQL can be set up to store a checksum with every page (I think that's even the default in recent releases). If an external process encrypts a data file used by PostgreSQL it is unlikely to get the checksums correct (unless it was written explicitely with PostgreSQL in mind). So the next time PostgreSQL reads some data from that file it will notice that the data is corrupted. Of course is would notice that anyway since all the other structures it expects aren't there either. > answer can be as simple as: when postgres do crash. Yep. That's what I would expect to happen pretty quickly on a busy database. The question is: Does that help you? At that point the data is already gone (at least partially), and you can only restore it from backup. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: libpq and mysterious "invalid byte sequence for encoding UTF8".
=?UTF-8?B?SmnFmcOtIFBhdmxvdnNrw70=?= writes: > Client and server encoding is UTF8. Locales are set to C.UTF8. If lc_messages is C then no translation of error strings would happen, so that theory goes out the window. Oh well. Perhaps you could attach gdb to the backend and get a stack trace from the call to errfinish? https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend regards, tom lane
Re: permission denied for large object 200936761
On 2/2/21 4:12 AM, Andrus wrote: Hi! So? What is your point? Somebody created a large object of size 0. report table has bytea column. It looks like psqlodbc driver adds ::lo cast when inserting binary data: https://github.com/hlinnaka/psqlodbc/blob/master/convert.c#L4564 and this adds row to pg_largeobject_metadata table. > Why it adds cast to lo type ? This type does not exist in Postgres server and causes server error. The comment for the code snippet you linked to is: "/* * the oid of the large object -- just put that in for the * parameter marker -- the data has already been sent to * the large object */" So at that point the deed has been done. The questions to ask: 1) Why the driver thinks it is being passed a large object in the first place? 2) Have there been any recent changes to code that passes through the ODBC driver that would account for 1)? 3) To help with 2), where is 'INSERT INTO report ( ... ) values (.. , '200936767'::lo, ... )" coming from? My suspicion is that it is user initiated change. If it is not and you suspect the ODBC driver then I would suggest bringing it up on the -odbc list: https://www.postgresql.org/list/pgsql-odbc/ Andrus. -- Adrian Klaver adrian.kla...@aklaver.com
pg_dumpall and tablespaces
Hello all, I have a dump file obtained from pg_dumpall on a MAC computer. I need to load in onto my Linux laptop running postgres. My scenario is software development. I'm trying to load the dump onto my Pg installation running on Linux (and later possibly on Linux over Docker) in order to perform my sw development against a fresh copy of the data. I got 2 problems concerning tablespaces: a) during the restore step I get lots of errors about the necessity to have root permissions to re-create the tablespaces and b) the tablespaces paths on the dump file are bound to the MAC filesystem (/Users//pg/). I would need to re-write that path to my home folder or '/var/lib/' Obviously I'm a bit confused on how to do this. pg_dumpall is very powerfull and simple, specially when using the "--create" option that does all the work for me. I would appreciate any directions on how to restore that dump thank you Joao
Re: pg_dumpall and tablespaces
On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote: Hello all, I have a dump file obtained from pg_dumpall on a MAC computer. I need to load in onto my Linux laptop running postgres. My scenario is software development. I'm trying to load the dump onto my Pg installation running on Linux (and later possibly on Linux over Docker) in order to perform my sw development against a fresh copy of the data. I got 2 problems concerning tablespaces: a) during the restore step I get lots of errors about the necessity to have root permissions to re-create the tablespaces and b) the tablespaces paths on the dump file are bound to the MAC filesystem (/Users//pg/). I would need to re-write that path to my home folder or '/var/lib/' Obviously I'm a bit confused on how to do this. pg_dumpall is very powerfull and simple, specially when using the "--create" option that does all the work for me. I would appreciate any directions on how to restore that dump Do you want to maintain tablespaces on the dev machine? If not from here: https://www.postgresql.org/docs/12/app-pg-dumpall.html --no-tablespaces Do not output commands to create tablespaces nor select tablespaces for objects. With this option, all objects will be created in whichever tablespace is the default during restore. This would have to be done when the pg_dumpall is run. thank you Joao -- Adrian Klaver adrian.kla...@aklaver.com
Re: permission denied for large object 200936761
Hi! So at that point the deed has been done. The questions to ask: 1) Why the driver thinks it is being passed a large object in the first place? Source data type was binary. It was mapped to oid for unknown reason. 2) Have there been any recent changes to code that passes through the ODBC driver that would account for 1)? 3) To help with 2), where is 'INSERT INTO report ( ... ) values (.. , '200936767'::lo, ... )" coming from? My suspicion is that it is user initiated change. This change was done by me. I added new reports from other database. Reports contain primary columns and import throws error about unknown lo type. I removed bonary columns from import and after that import succeeds. It looks like during this procces 3 rows were added to large object metadata table. I used select lo_unlink(oidnumber) to remove them. >If it is not and you suspect the ODBC driver then I would suggest bringing it up on the -odbc list: >https://www.postgresql.org/list/pgsql-odbc/ I created binary data in client side creating FoxPro cursor create cursor t ( t gen ) and used psqlodbc to insert this data: create temp table test ( test bytea ) on commit drop; insert into test values ( ?t.t ); This code throws exception type "lo" does not exist but each call adds new row to pg_largeobject_metadata table. Odbc driver creates large object and adds lo cast. This large object remains even if transaction is rolled back due to unexisting lo type. C7=0 (bytea as logvarbinary is false) is used in connection string. Andrus.
Re: pg_dumpall and tablespaces
On Tue, Feb 2, 2021 at 4:52 PM Adrian Klaver wrote: > On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote: > > > I got 2 problems concerning tablespaces: > > a) during the restore step I get lots of errors about the necessity to > > have root permissions to re-create the tablespaces and > > b) the tablespaces paths on the dump file are bound to the MAC > > filesystem (/Users//pg/). I would need to re-write that path to > > my home folder or '/var/lib/' > > Do you want to maintain tablespaces on the dev machine? > > If not from here: > > https://www.postgresql.org/docs/12/app-pg-dumpall.html > > --no-tablespaces > > Do not output commands to create tablespaces nor select tablespaces > for objects. With this option, all objects will be created in whichever > tablespace is the default during restore. > > > This would have to be done when the pg_dumpall is run. > That's great. I really do not need the tablespaces for dev. I will try that thank you
Re: pg_dumpall and tablespaces
Adrian Klaver writes: > On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote: >> I have a dump file obtained from pg_dumpall on a MAC computer. I need to >> load in onto my Linux laptop running postgres. >> I got 2 problems concerning tablespaces: >> a) during the restore step I get lots of errors about the necessity to >> have root permissions to re-create the tablespaces and >> b) the tablespaces paths on the dump file are bound to the MAC >> filesystem (/Users//pg/). I would need to re-write that path to >> my home folder or '/var/lib/' > Do you want to maintain tablespaces on the dev machine? > If not from here: > https://www.postgresql.org/docs/12/app-pg-dumpall.html > --no-tablespaces Also, if you're not in a position to re-make the dump file, you can just restore it and ignore all the tablespace-related errors. You'll end up with the same situation either way, i.e. all the tables exist in the default tablespace. If you do need to preserve the separation into distinct tablespaces, you could try this: * Starting with an empty installation, create the tablespaces you need, matching the original installation's tablespace names but putting the directories wherever is handy. * Restore the dump, ignoring the errors about tablespaces already existing. Either way, the key is that a dump file is just a SQL script and isn't especially magic; you don't have to be in fear of ignoring a few errors. pg_dump builds the script to be resistant to certain types of issues, and missing tablespaces is one of those. I do recommend capturing the stderr output and checking through it to ensure you didn't have any unexpected errors. regards, tom lane
Re: pg_dumpall and tablespaces
On Tue, Feb 2, 2021 at 5:08 PM Tom Lane wrote: > Adrian Klaver writes: > > On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote: > >> I have a dump file obtained from pg_dumpall on a MAC computer. I need > to > >> load in onto my Linux laptop running postgres. > >> I got 2 problems concerning tablespaces: > >> a) during the restore step I get lots of errors about the necessity to > >> have root permissions to re-create the tablespaces and > >> b) the tablespaces paths on the dump file are bound to the MAC > >> filesystem (/Users//pg/). I would need to re-write that path to > >> my home folder or '/var/lib/' > > > Do you want to maintain tablespaces on the dev machine? > > > If not from here: > > https://www.postgresql.org/docs/12/app-pg-dumpall.html > > --no-tablespaces > > Also, if you're not in a position to re-make the dump file, you > can just restore it and ignore all the tablespace-related errors. > You'll end up with the same situation either way, i.e. all the > tables exist in the default tablespace. > > If you do need to preserve the separation into distinct tablespaces, > you could try this: > > * Starting with an empty installation, create the tablespaces you need, > matching the original installation's tablespace names but putting > the directories wherever is handy. > > * Restore the dump, ignoring the errors about tablespaces already > existing. > > Either way, the key is that a dump file is just a SQL script and > isn't especially magic; you don't have to be in fear of ignoring > a few errors. pg_dump builds the script to be resistant to certain > types of issues, and missing tablespaces is one of those. > > I do recommend capturing the stderr output and checking through it > to ensure you didn't have any unexpected errors. > > regards, tom lane > Hi Tom, thanks for the additional details. I did not know about that kind of tolerance during restore. Cheers Thank you
Re: permission denied for large object 200936761
On 2/2/21 9:05 AM, Andrus wrote: Hi! and used psqlodbc to insert this data: create temp table test ( test bytea ) on commit drop; insert into test values ( ?t.t ); This code throws exception type "lo" does not exist but each call adds new row to pg_largeobject_metadata table. Odbc driver creates large object and adds lo cast. This large object remains even if transaction is rolled back due to unexisting lo type. C7=0 (bytea as logvarbinary is false) is used in connection string. I think that is supposed to be true for what you want to do. It is has been awhile since I worked with ODBC so I would confirm on the -odbc list. Andrus. -- Adrian Klaver adrian.kla...@aklaver.com
Re: ransomware
Marc Millas writes: > Hi, > > I know its quite general. It is as I dont know what approaches may exist. > > Requirement is extremely simple: Is there anyway, from a running postgres > standpoint, to be aware that a ransomware is currently crypting your data ? > > answer can be as simple as: when postgres do crash. > > something else ? > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com > > > Ransomeware tends to work at the disk level rather than the application level. There is too much work/effort required to focus ransomeware at an application level because of the amount of variation in applications and versions, to be profitable. This means any form of detection you may try to implement really needs to be at the disk level, not the application level. While it could be possible to add some sort of monitoring for encryption/modification to underlying data files, by the time this occurs, it will likely be too late (and unless your monitoring is running on a different system, the binaries/scripts are likely also encrypted and won't run as well). The best protection from ransomeware is a reliable, regular and TESTED backup and restoration solution which runs frequently enough that any lost data is acceptable from a business continuity position and which keeps multiple backup versions in case your ransomeware infection occurs some time before it is actually triggered i.e. in case your most recent backups are already infected. Backups should be stored in multiple locations. For large data sets, this can often mean having the ability to take fast filesystem snapshots as more traditional 'copy' approaches are often too slow to perform backups frequently enough to meet business continuity requirements. Bar far, the most common failure in backup solutions is around failure to test the restoration component. I've seen way too many places where they thought they had adequate backups only to find when they needed to perform a restoration, key data was missing. This can greatly increase the time it takes to perform a restoration and in extreme cases can mean restoration is not possible. regular testing of restoration processes is critical to any reliable backup solution. As it is also a good idea to have some sort of testing/staging environment for testing code/configuration changes, new versions etc, it can make sense to use your backups as part of your staging/testing environment 'refresh' process. A regular refresh of your staging/testing environment from backups then provides you with assurances your backups are working and that your testing etc is being performed on systems with data most similar to your production systems. Tim
Re: Segmentation fault on startup
(again to the list...) Am 01.02.21 um 21:40 schrieb Tom Lane: Helmut Bender writes: I'm running a nextcloud server in a docker container on an RasPi 4 (only SSD, no SD), which uses PostgreSQL 10 as server. 10.what? We're already up to 15 patch releases for that branch. As I use the docker image, it seems to be at 10.15. Today I had to restart the RasPi. Now the container fails to start with a segmentation violation. Not good --- sounds like you have data corruption. After an OS crash this is something that's quite possible if you haven't taken the time to qualify the storage subsystem's honoring of fsync. Well, it was a regular reboot... don't know what happend. If it's a reasonably recent dump, you might end up just having to re-initdb and restore the dump. OK, so there's no way to repair? Well, I make a daily backup, so that is not the problem. As with all else computer-related, there's no substitute for a good backup plan :-( Oh yes. And when you do, be shure to backup anything you need. I managed to fiddle the backup into my container (which I updated to PGSQL 11 btw). BUT - it complained about missing roles. So don't forget to pg_dumpall --roles-only when you pg_dump! Thank you for your tips, it's running again. :-D -- Gruß Helmut
Re: count(*) vs count(id)
Hello list
My English is not very good, so I pretend that through the examples you
understand what I intend to expose
-- Recreate the query that is supposedly wrong
select calendar.entry, count(*)
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date
group by calendar.entry;
-- wrong???
entry | count
+---
2020-08-20 00:00:00-05 | 1
2020-08-21 00:00:00-05 | 1
2020-08-22 00:00:00-05 | 1
2020-08-23 00:00:00-05 | 1
2020-08-24 00:00:00-05 | 1
2020-08-25 00:00:00-05 | 1
2020-08-26 00:00:00-05 | 1
2020-08-27 00:00:00-05 | 1
2020-08-28 00:00:00-05 | 2
2020-08-29 00:00:00-05 | 1
2020-08-30 00:00:00-05 | 1
2020-08-31 00:00:00-05 | 1
2020-09-01 00:00:00-05 | 1
2020-09-02 00:00:00-05 | 2
2020-09-03 00:00:00-05 | 1
2020-09-04 00:00:00-05 | 2
2020-09-05 00:00:00-05 | 1
2020-09-06 00:00:00-05 | 1
2020-09-07 00:00:00-05 | 1
2020-09-08 00:00:00-05 | 1
2020-09-09 00:00:00-05 | 1
2020-09-10 00:00:00-05 | 1
2020-09-11 00:00:00-05 | 1
2020-09-12 00:00:00-05 | 1
2020-09-13 00:00:00-05 | 1
2020-09-14 00:00:00-05 | 1
2020-09-15 00:00:00-05 | 1
-- In the count I will only consider the records of call_records
select calendar.entry, count(call_records.*)
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date
group by calendar.entry;
--- perfect
entry | count
+---
2020-08-20 00:00:00-05 | 0
2020-08-21 00:00:00-05 | 0
2020-08-22 00:00:00-05 | 0
2020-08-23 00:00:00-05 | 0
2020-08-24 00:00:00-05 | 0
2020-08-25 00:00:00-05 | 0
2020-08-26 00:00:00-05 | 0
2020-08-27 00:00:00-05 | 0
2020-08-28 00:00:00-05 | 2
2020-08-29 00:00:00-05 | 1
2020-08-30 00:00:00-05 | 0
2020-08-31 00:00:00-05 | 0
2020-09-01 00:00:00-05 | 1
2020-09-02 00:00:00-05 | 2
2020-09-03 00:00:00-05 | 1
2020-09-04 00:00:00-05 | 2
2020-09-05 00:00:00-05 | 0
2020-09-06 00:00:00-05 | 0
2020-09-07 00:00:00-05 | 0
2020-09-08 00:00:00-05 | 0
2020-09-09 00:00:00-05 | 0
2020-09-10 00:00:00-05 | 0
2020-09-11 00:00:00-05 | 0
2020-09-12 00:00:00-05 | 0
2020-09-13 00:00:00-05 | 0
2020-09-14 00:00:00-05 | 0
2020-09-15 00:00:00-05 | 0
when placing * I want to bring all the join records between both tables and
when counting them of course there will be a row for the dates 2020-08-30
, 2020-08-31 so the call_records fields are null
select *
from generate_series('2020-08-20'::date, '2020-09-15'::date, '1
day'::interval) as calendar(entry)
left join (values (1,'2020-08-28 09:44:11'::timestamp),
(2,'2020-08-28 10:44:11'::timestamp),
(3,'2020-08-29 11:44:11'::timestamp),
(4,'2020-09-01 02:44:11'::timestamp),
(5,'2020-09-02 03:44:11'::timestamp),
(6,'2020-09-02 04:44:11'::timestamp),
(7,'2020-09-03 05:44:11'::timestamp),
(8,'2020-09-04 06:44:11'::timestamp),
(10,'2020-09-04 07:44:11'::timestamp)) as call_records(id,timestamp)
on calendar.entry = call_records.timestamp::date
entry | id | timestamp
++-
2020-08-20 00:00:00-05 ||
2020-08-21 00:00:00-05 ||
2020-08-22 00:00:00-05 ||
2020-08-23 00:00:00-05 ||
2020-08-24 00:00:00-05 ||
2020-08-25 00:00:00-05 ||
2020-08-26 00:00:00-05 ||
2020-08-27 00:00:00-05 ||
2020-08-28 00:00:00-05 | 1 | 2020-08-28 09:44:11
2020-08-28 00:00:00-05 | 2 | 2020-08-28 10:44:11
2020-08-29 00:00:00-05 | 3 | 2020-08-29 11:44:11
2020-08-30 00:00:00-05 ||
2020-08-31 00:00:00-05 ||
2020-09-01 00:00:00-05 | 4 | 2020-09-01 02:44:11
2020-09-02 00:00:00-05 | 5 | 2020-09-02 03:44:11
2020-09-02 00:00:00-05 | 6 | 2020-09-02 04:44:11
2020-09-03 00:00:00-05 | 7 | 2020-09-03 05:4
permission denied to create and drop user
Hello all,
I have just installed postgresql on Debian stable (from debian apt
archives). It seems that the postgres user does not have permissions to
DROP/CREATE USER. I was expecting the postgres user to be a superuser but
something seems weird. my postgres user does not have the usual superuser
attributes.
Please see my results bellow:
root@deb10tp:/# id
uid=0(root) gid=0(root) groups=0(root)
root@deb10tp:/# pwd
/
root@deb10tp:/# su postgres
postgres@deb10tp:/$ psql
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.
postgres=> DROP USER foo;
ERROR: permission denied to drop role
postgres=> DROP USER postgres;
ERROR: permission denied to drop role
postgres=> CREATE USER foo;
ERROR: permission denied to create role
postgres=> \du
List of roles
Role name | Attributes | Member of
---++---
pgcon || {}
postgres || {}
How do I get a superuser/postgres that is capable to CREATE / DROP another
user? or a user that might allowed to do anything?
Thank you
Joao
Re: permission denied to create and drop user
On 2/2/21 1:58 PM, Joao Miguel Ferreira wrote:
Hello all,
I have just installed postgresql on Debian stable (from debian apt
archives). It seems that the postgres user does not have permissions to
DROP/CREATE USER. I was expecting the postgres user to be a superuser
but something seems weird. my postgres user does not have the usual
superuser attributes.
Please see my results bellow:
root@deb10tp:/# id
uid=0(root) gid=0(root) groups=0(root)
root@deb10tp:/# pwd
/
root@deb10tp:/# su postgres
postgres@deb10tp:/$ psql
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.
Not sure how you can do above when below the postgres user does not have
LOGIN attribute?
What where the exact steps you took when you did the install?
postgres=> DROP USER foo;
ERROR: permission denied to drop role
postgres=> DROP USER postgres;
ERROR: permission denied to drop role
postgres=> CREATE USER foo;
ERROR: permission denied to create role
postgres=> \du
List of roles
Role name | Attributes | Member of
---++---
pgcon | | {}
postgres | | {}
How do I get a superuser/postgres that is capable to CREATE / DROP
another user? or a user that might allowed to do anything?
Thank you
Joao
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: permission denied to create and drop user
Joao Miguel Ferreira writes: > I have just installed postgresql on Debian stable (from debian apt > archives). It seems that the postgres user does not have permissions to > DROP/CREATE USER. I was expecting the postgres user to be a superuser but > something seems weird. my postgres user does not have the usual superuser > attributes. That is weird. Maybe just drop the cluster and re-initdb? It might be worth checking the debian postgres package's documentation to see if they're throwing you some kind of curveball. One thing I was about to suggest is that the bootstrap superuser might not be named postgres (it'll be named after whichever OS user ran initdb). However, your "\du" output pretty clearly shows you have no superuser, and that's just odd. regards, tom lane
Re: permission denied to create and drop user
Hi, On Tue, Feb 2, 2021 at 10:30 PM Tom Lane wrote: > Joao Miguel Ferreira writes: > > I have just installed postgresql on Debian stable (from debian apt > > archives). It seems that the postgres user does not have permissions to > > DROP/CREATE USER. I was expecting the postgres user to be a superuser but > > something seems weird. my postgres user does not have the usual superuser > > attributes. > > That is weird. Maybe just drop the cluster and re-initdb? > > It might be worth checking the debian postgres package's documentation to > see if they're throwing you some kind of curveball. One thing I was about > to suggest is that the bootstrap superuser might not be named postgres > (it'll be named after whichever OS user ran initdb). However, your "\du" > output pretty clearly shows you have no superuser, and that's just odd. > > regards, tom lane > I'm sorry about the confusion. I have just realized that the loss of superuser attributes was due to my dump file. Debian does the right thing. During "apt install" it is possible to see the log lines stating that the superuser is actually postgres. that seems fine. But... my dump file contains some agressive commands that are actually making a reall mess. here they are: CREATE ROLE pgcon; ALTER ROLE pgcon WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE postgres; ALTER ROLE postgres WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; that is why I messed up my database. Lucky it is not critical at all. I can start all over again, no problem. maybe "pg_dumpall" has options to avoid those changes?
Re: permission denied to create and drop user
On 2/2/21 2:43 PM, Joao Miguel Ferreira wrote: Hi, But... my dump file contains some agressive commands that are actually making a reall mess. here they are: CREATE ROLE pgcon; ALTER ROLE pgcon WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; CREATE ROLE postgres; ALTER ROLE postgres WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS; that is why I messed up my database. Lucky it is not critical at all. I can start all over again, no problem. maybe "pg_dumpall" has options to avoid those changes? That would be attributing AI abilities to pg_dumpall that it does not have. Those commands got there from someone in the original database tinkering with grants. -- Adrian Klaver adrian.kla...@aklaver.com
Re: count(*) vs count(id)
I just ran a few practical tests on large (~14mil rows) tables that have multiple indexes. SELECT COUNT(id) forces PostgreSQL to use the primary key index. SELECT COUNT(*) allows PostgreSQL to chose an index to use and it seems to be choosing one of smaller size which leads to less IO and hence returns the result faster. On Tue, Feb 2, 2021 at 3:45 PM Hellmuth Vargas wrote: > >
