Cause: org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.1946998112" to 8388608 bytes: No space left on device
Hello team, I'm getting below error while accessing postgres11 database. Please suggest the solution for this issue. Cause: org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.1946998112" to 8388608 bytes: No space left on device at com.caucho.el.ArrayResolverExpr.invoke(ArrayResolverExpr.java:260) Details from docker : bash-4.4$ mount | grep /dev/shm shm on /dev/shm type tmpfs (rw,context="system_u:object_r:container_file_t:s0:c127,c569",nosuid,nodev,noexec,relatime,size=65536k) bash-4.4$ free && ipcs -l && echo "page size:" && getconf PAGE_SIZE total used free sharedbuffers cached Mem: 32779840 246123008167540 0 52 23735916 -/+ buffers/cache: 876332 31903508 Swap: 4063228 911363972092 -- Messages: Limits max queues system wide = 16384 max size of message (bytes) = 8192 default max size of queue (bytes) = 16384 -- Shared Memory Limits max number of segments = 4096 max seg size (kbytes) = 18014398509465599 max total shared memory (pages) = 18446744073692774399 min seg size (bytes) = 1 -- Semaphore Limits max number of arrays = 128 max semaphores per array = 250 max semaphores system wide = 32000 max ops per semop call = 32 semaphore max value = 32767 page size: 4096 bash-4.4$ bash-4.4$ psql psql (11.2) Type "help" for help. postgres=# show max_parallel_workers_per_gather; max_parallel_workers_per_gather - 2 (1 row) postgres=# show max_parallel_workers_per_gather; max_parallel_workers_per_gather - 2 (1 row) postgres=# Thanks,
CREATE DATABASE ... TEMPLATE ... vs checksums
The application I am working on is doing database upgrades by cloning the previous release into a new database by means of CREATE DATABASE new_one TEMPLATE old_one ...; (health care, extremely conservative, contingency: can switch back to still existing old database with old client at any time without delay). For good measure, appropriate REINDEX / ANALYZE / VALIDATE CONSTRAINTS are applied judiciously. Now I wondered how to include a checksum verification step. Before the advent of pg_(verify_)checksums the canonical way to test checksums online (if enabled) was to do a dump of the objects desired to be checked, if I am informed correctly. The rationale being that for dumping an object it will need to be read, and consequently CRCs are being checked. So I was wondering whether creating a database from a template also does that. I assume it does not because likely that's a file level copy operation rather than a semantic data read, correct ? If so (that is: not), should there be a way of saying CREATE DATABASE new_one TEMPLATE old_one ... VERIFY_CRC; Likely, this proposal won't go very far since we can already see an explicit (online) SQL command select pg_verify_checksums(object); on the horizon, right ? So, for the time being, my only option would be to include a pg_dump step of the template before cloning it ? Thanks for insights shared, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: Questions about btree_gin vs btree_gist for low cardinality columns
I didn't notice Bloom filters in the conversation so far, and have been waiting for *years* for a good excuse to use a Bloom filter. I ran into them years back in Splunk, which is a distributed log store. There's an obvious benefit to a probabalistic tool like a Bloom filter there since remote lookup (and/or retrieval from cold storage) is quite expensive, relative to a local, hashed lookup. I haven't tried them in Postgres. In the case of a single column with a small set of distinct values over a large set of rows, how would a Bloom filter be preferable to, say, a GIN index on an integer value? I have to say, this is actually a good reminder in my case. We've got a lot of small-distinct-values-big-rows columns. For example, "server_id", "company_id", "facility_id", and so on. Only a handful of parent keys with many millions of related rows. Perhaps it would be conceivable to use a Bloom index to do quick lookups on combinations of such values within the same table. I haven't tried Bloom indexes in Postgres, this might be worth some experimenting. Is there any thought in the Postgres world of adding something like Oracle's bitmap indexes?
RE: Questions about btree_gin vs btree_gist for low cardinality columns
> In the case of a single column with a small set of distinct values over a > large set of rows, how would a Bloom filter be preferable to, say, a GIN > index on an integer value? I don't think it would - it's probably better suited to the multi-column case you described previously. > I have to say, this is actually a good reminder in my case. We've got a lot > of small-distinct-values-big-rows columns. For example, "server_id", > "company_id", "facility_id", and so on. Only a handful of parent keys with > many millions of related rows. Perhaps it would be conceivable to use a Bloom > index to do quick lookups on combinations of such values within the same > table. I haven't tried Bloom indexes in Postgres, this might be worth some > experimenting. Yes, this is more like the use case I was thinking of. Steve.
Re: Postgresql backup via LVM snapshot?
Hi,
I suggest you to take a serious look at ZFS: https://zfsonlinux.org/
With simply a few commands, mainly zpool and zfs, you can manage
everything in one stack:
* encryption: new in 0.8.0
* compression: with lz4, it's very lightweitgh with practically no
impact on cpu
* snapshots: it uses copy on write, no messing with complicated lvm
snapshot management. Instantaneous snapshots. And as a bonus you can
easily copy snapshots between hosts.
* arc, l2arc: better cache management than page cache on linux, not
simply an LRU
* it handles mount point for you
I don't see any problems to you procedure, just keep your generated WALs
during the backup procedure (pg-start-backup to pg-stop-backup).
Generally, you'll want to separate WAL directory from the data one.
Because I can't cover more in a simple email (tuning, parameters,
disable pg full page writes, disable pg page checksuming, etc.), here
are good resources:
* https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-zfs/
* https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices
* https://www.slideshare.net/SeanChittenden/postgres-on-zfs-lightning-talk
* From Oracle for Oracle, but it can helps:
o https://docs.oracle.com/cd/E36784_01/html/E36845/chapterzfs-db2.html
o
https://docs.oracle.com/cd/E53394_01/html/E54818/chapterzfs-1.html#scrolltoc
o
https://www.oracle.com/technetwork/server-storage/solaris10/config-solaris-zfs-wp-167894.pdf
Maybe someone else can share experience with PG + ZFS ?
Hope that helps
Bruno Lavoie
On 2019-05-30 3:38 p.m., Lu, Dan wrote:
Hello,
Would you be able to confirm for me that a host level LVM snapshot of
the PGDATA directory along with PG_WAL directly via LVM backup is
supported way of backup and restore?
I read about this here:
https://dba.stackexchange.com/questions/145361/backup-standby-database-using-lvm-snapshot
Is it as simple as:
èSELECT pg_start_backup('Begin LVM Backup At xyz');
èDo LVM backup from o/s end to backup PGDATA/PG_WAL
èSELECT pg_stop_backup();
Thanks.
Dan
IMPORTANT: The information contained in this email and/or its
attachments is confidential. If you are not the intended recipient,
please notify the sender immediately by reply and immediately delete
this message and all its attachments. Any review, use, reproduction,
disclosure or dissemination of this message or any attachment by an
unintended recipient is strictly prohibited. Neither this message nor
any attachment is intended as or should be construed as an offer,
solicitation or recommendation to buy or sell any security or other
financial instrument. Neither the sender, his or her employer nor any
of their respective affiliates makes any warranties as to the
completeness or accuracy of any of the information contained herein or
that this message or any of its attachments is free of viruses.
Re: Postgresql backup via LVM snapshot?
Are you trying to fit the postgres backups in some overarching LVM-based
backup scheme you're already running or is this from scratch?
With the possible exception of rsync's deltas & hardlink trickeries
between full backups, I personally don't see a point in using a
block/file-level copy for physical backups of Postgres clusters (unless
you're keeping the snapshot around as the "thin backup" but that would
put the backup in the same volume group as the live data; meh).
pg_basebackup (or more specifically the underlying replication protocol
command BASE_BACKUP), precisely wraps up all that you're trying to
accomplish here; it is perfectly aware of the internal state of
operations and guarantees operations
synchronisation/correctness/completeness.
It roughly boils down to (with minor variations between types of
physical backups):
- dropping a checkpoint and wait for it to complete, or wait for the
next one to roll around (that will be the starting point of WAL recovery
when you restore)
- remember the WAL location of that checkpoint
- copy the data files "as is" without worrying about torn/corrupt pages
due to changes. They will be overwritten with the amended version during
WAL recovery
- optionally put away a copy of all the transaction logs that were
generated between the checkpoint and the end of the copy if you're not
already performing WAL archival (or if you simply want your backup to be
self-contained and recoverable without accessing the WAL archive).
The database will look to have crashed when you initially restore it
(same as with an any form of snapshotting including LVM) and will have
to go through WAL recovery anyway: the earliest version of the DB to
which you can safely restore is the one at the moment the copy ended, so
the benefits of the atomic low-level snapshot are negated.
By using the old exclusive start/stop_backup() & action synchronisation
yourself you're just making it more manual and error prone.
Regards
F
On 30/05/2019 20:38, Lu, Dan wrote:
Hello,
Would you be able to confirm for me that a host level LVM snapshot of
the PGDATA directory along with PG_WAL directly via LVM backup is
supported way of backup and restore?
I read about this here:
https://dba.stackexchange.com/questions/145361/backup-standby-database-using-lvm-snapshot
Is it as simple as:
èSELECT pg_start_backup('Begin LVM Backup At xyz');
èDo LVM backup from o/s end to backup PGDATA/PG_WAL
èSELECT pg_stop_backup();
Thanks.
Dan
IMPORTANT: The information contained in this email and/or its
attachments is confidential. If you are not the intended recipient,
please notify the sender immediately by reply and immediately delete
this message and all its attachments. Any review, use, reproduction,
disclosure or dissemination of this message or any attachment by an
unintended recipient is strictly prohibited. Neither this message nor
any attachment is intended as or should be construed as an offer,
solicitation or recommendation to buy or sell any security or other
financial instrument. Neither the sender, his or her employer nor any of
their respective affiliates makes any warranties as to the completeness
or accuracy of any of the information contained herein or that this
message or any of its attachments is free of viruses.
--
Regards
Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology
--
Notice: This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.
If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.
Fetch and Sizzle
are trading names of Speciality Stores Limited and Fabled is a trading name
of Marie Claire Beauty Limited, both members of the Ocado Group.
References to the “Ocado Group” are to Ocado Group plc (registered in
England and Wales with number 7098618) and its subsidiary undertakings (as
that expression is defined in the Companies Act 2006) from time to time.
The registered office of Ocado Group plc is Buildings One & Two, Trident
Place, Mosquito Way, Hatfield, Hertfordshire, AL10 9UL.
One way replication in PostgreSQL
Hello, If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ? If possible, I would prefer partial replication (only some tables) to full base replication (all instances). Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ? Thanks in advance - Météo-France - PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
Re: One way replication in PostgreSQL
Greetings, * PALAYRET Jacques (jacques.palay...@meteo.fr) wrote: > If, for security reasons, I can't create a connection or a flow from > subscriber/secundary/slave towards provider/primary/master, witch replication > systems can I use ? The simplest approach might be to use WAL shipping with one of the various backup tools that do that- eg: you could use pgbackrest in the archive_command of the primary system and push WAL to a repo that's on the replica (or some other system that the replica is allowed to connect to). > If possible, I would prefer partial replication (only some tables) to full > base replication (all instances). Just to be clear, you couldn't use a WAL-based shipping method for partial replication (at least, not today anyway). Thanks, Stephen signature.asc Description: PGP signature
Re: One way replication in PostgreSQL
Hi, On 6/3/19 6:00 PM, PALAYRET Jacques wrote: > Hello, > > If, for security reasons, I can't create a connection or a flow from > subscriber/secundary/slave towards provider/primary/master, witch > replication systems can I use ? > To perform replication, you need some form of connectivity between the hosts (unless, you want to only apply archived WAL files, as mentioned by Stephen here above). In streaming replication your replica needs to be able to initiate the connection to master If you instead have, let's say, master -> replica or both your hosts can reach a 3rd host, you might work around the problem using an SSH tunnel. I discourage you from this latter option, anyway. > If possible, I would prefer partial replication (only some tables) to > full base replication (all instances). you could have a look into pglogical regards, fabio pardi
Re: Cause: org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.1946998112" to 8388608 bytes: No space left on device
On Mon, Jun 3, 2019 at 5:56 AM Daulat Ram wrote: > Cause: org.postgresql.util.PSQLException: ERROR: could not resize shared > memory segment "/PostgreSQL.1946998112" to 8388608 bytes: No space left on > device > shm on /dev/shm type tmpfs > (rw,context="system_u:object_r:container_file_t:s0:c127,c569",nosuid,nodev,noexec,relatime,size=65536k) I don't use Docker myself but I believe you can either tell it to expose the host's /dev/shm or you can start it with something like --shm-size="4096m". It's surprisingly difficult to know how much shared memory PostgreSQL really needs, today. Without parallel query, it's complicated but roughly: you can use work_mem for each executor node, and that is (at a first approximation) linked to how many joins there are in your query. With the advent of parallel query, it's multiplied by the number of workers. If any of the joins happen to be parallel hash join, then the memory comes out of shared memory instead of private memory. It's not fundamentally different in terms of the amount needed, but Docker users are forced to confront the question so they can set --shm-size. One argument is that you should set it sky-high, since private memory has no such limit, and from a virtual memory point of view it's all the same in the end, it's just memory. The difficulty with choosing a limit here is that --shm-size is a system-wide limit, but PostgreSQL not only has no system-wide memory budgeting, it doesn't even have a per-query memory budget. It just has this "per operation" (usually executor node) thing. The total amount of memory you need to run PostgreSQL queries is a function of work_mem * number of concurrent queries you expect to run * number of tables you expect them to join * number of parallel workers you expect to run. The amount of it that happens to be in /dev/shm on a Linux system (rather than private memory) is controlled by what fraction of your joins are parallel hash joins. Making our memory limits better is really hard. -- Thomas Munro https://enterprisedb.com
Requirement PA-DSS 1.1.4
Hi, We've build a Payments Authorisation system (Box solution) on Postgresql database and now we are hitting following issue with our PA:DSS audit - requirement PA-DSS 1.1.4: <> 1.1.4 Securely delete any track data (from the magnetic stripe or equivalent data contained on a chip), card verification values or codes, and PINs or PIN block data stored by previous versions of the payment application, in accordance with industry-accepted standards for secure deletion, as defined, for example by the list of approved products maintained by the National Security Agency, or by other State or National standards or regulations. All of these elements of sensitive authentication data are not permitted to be stored post-authorization. If older versions of payment applications stored this information, the payment application vendor is required to provide instructions in the PA-DSS Implementation Guide as well as a secure wipe tool or procedure. If not securely deleted, this data could remain hidden on customer systems, and malicious individuals who obtain access to this information could use it to produce counterfeit payment cards, and/or to perform fraudulent transactions. Unfortunately, description is too ambiguous and our QSA claims that stored is stored regardless of form. Tokens he can live with, but encryption not. But we do encryption (regardless it is happening with a key stored on HSM). Actual trouble comes with forensics: <> 1.1.4.c Verify, through the use of forensic tools and/or methods, that the secure wipe tool or procedure provided by vendor securely removes the data, in accordance with industry-accepted standards for secure deletion of data. Similar with: <> 2.6 Provide a mechanism to render irretrievable any cryptographic key material or cryptogram stored by the payment application, in accordance with industry-accepted standards. These are cryptographic keys (Host stored HSM keys) used to encrypt or verify cardholder data. At this stage our QSA was able to identify that data remain on a persistence device (DB files) even after deleting those from our application. Checking SQLite database, it comes with pragma secure_delete - which is very much what we are looking for. https://www.sqlite.org/pragma.html#pragma_secure_delete I would appreciate your input on this. Is there any solution already I haven't been able to find in documentation. If not, is there any way we can put this on a road map or even contribute to your code? Thank you in advance & Kind Regards, Jan -- EFTlab CTO
Re: Questions about btree_gin vs btree_gist for low cardinality columns
On Sun, Jun 2, 2019 at 4:07 PM Tom Lane wrote: > Anyway, I said to Jeremy in the hallway that it might not be that > hard to bolt IOS support onto GIN for cases where the opclass is > a non-subdividing one, but after looking at the code I'm less sure > about that. GIN hasn't even got an "amgettuple" code path, just > "amgetbitmap", and a big part of the reason why is the need to merge > results from the fastupdate pending list with results from the main > index area. Not sure how we could deal with that. I suspect that GIN also avoids several other problems by only offer an "amgetbitmap", and not an "amgettuple". For example, it doesn't have to worry about things like numeric display scale, where a datum is substantively different to another datum, while still being equal according to opclass semantics (this is an example that I end up citing in many discussions about indexing). I bet that there are a few more of those beyond those two, that I haven't thought about. > Anyway, the larger point here is that right now btree_gin is just a quick > hack, and it seems like it might be worth putting some more effort into > it, because the addition of duplicate-compression changes the calculus > for whether it's useful. There was also discussion about making nbtree support deduplication during the hallway track. Jim Finnerty reminded me that there is a patch from Anastasia that did deduplication in nbtree that didn't go anywhere. Heikki independently talked about the possibility that he would work on this project in the next release, without being prompted by me. I think that the fact that nbtree sorts duplicate entries in heap TID order these days makes that worth looking into again. We can use something like GIN's varbyte encoding process to compress duplicates effectively. A lot of the problems (the numeric display scale problem, pg_upgrade) can be avoided by defining deduplication as something that happens on a best-effort basis. This is not the case within GIN, where it's impossible for the main entry tree to have duplicates without at least storing them in a posting list. -- Peter Geoghegan
Re: One way replication in PostgreSQL
You could use FDW to replicate what you need to an external server from the provider/primary/master to the subscriber/secondary/slaveUsing triggers on the master tables that you want to replicate, you can execute the insert/update/delete actions on the secondary tables through the FDW.With this approach you only need a connection from provider to the subscriber. Regards On Mon, 2019-06-03 at 18:00 +0200, PALAYRET Jacques wrote: > Hello, > > If, for security reasons, I can't create a connection or a flow from > subscriber/secundary/slave towards provider/primary/master, witch > replication systems can I use ? > > If possible, I would prefer partial replication (only some tables) to > full base replication (all instances). > > Do trigger-based replication systems (like Slony or Londiste or > others) need a connection or flow from subscriber to the provider ? > > Thanks in advance > - Météo-France - > PALAYRET JACQUES > DCSC/MBD > jacques.palay...@meteo.fr > Fixe : +33 561078319
csvlog Behavior when log file missing
Hi all; I have a system where postgres seems to be randomly dying. When researching the issue I, of course, began looking at logs. I found that much of the logs were missing due to logrotate configuration on the server. Logs were being rotated by size and then compressed. Because of this, the first rotation would take the log file out from underneath the database and it is not recreated. My suspicion is that the database eventually crashes due to inability to log. The documentation says the logging_collector will halt other processes if it is unable to log. However, it seems that the system continues to run fine for several days before crashing and applications using the database continue to operate. Does the logrotate configuration possibly explain the behavior? I can add the 'copytruncate' option to the logrotate configuration and see if the issue resolves, but I'd like to also understand the issue. Also, if I add the 'stderr' destination and continue to rotate the csv log file out, might I catch errors related to the missing csv log or would the logging_collector simply continue operating since it still has access to the stderr log? Thanks! LKimmel, RHCE, VCP7-CMA
Re: Questions about btree_gin vs btree_gist for low cardinality columns
On Sun, Jun 2, 2019 at 7:07 PM Tom Lane wrote: > Jeff Janes writes: > > On Fri, May 24, 2019 at 11:26 AM Jeremy Finzel > wrote: > >> I have been hoping for clearer direction from the community about > >> specifically btree_gin indexes for low cardinality columns (as well as > low > >> cardinality multi-column indexes). In general there is very little > >> discussion about this both online and in the docs. Rather, the emphasis > >> for GIN indexes discussed is always on full text search of JSON > indexing, > >> not btree_gin indexes. > > I just wanted to mention that Jeremy and I had a bit of hallway-track > discussion about this at PGCon. The core thing to note is that the GIN > index type was designed to deal with data types that are subdividable > and you want to search for individual component values (array elements, > lexemes in a text document, etc). The btree_gin extension abuses this > by just storing the whole values as if they were components. AFAIR, > the original idea for writing both btree_gin and btree_gist was to allow > creating a single multicolumn index that covers both subdividable and > non-subdividable columns. The idea that btree_gin might be used on its > own wasn't really on the radar, I don't think. Even before 9.4 btree_gin indexes with many duplicates were still much more compact than B-Tree, because the the value and the index tuple headers is not repeated for each TID the way it is in B-Tree. Of course TID list compression has made it better yet. I don't know what the original motivation was for btree_gin, but multi-column GIN indexes never made much sense to me anyway. What do they do that can't be done just as well by separate single-column indexes combined through BitmapAnd and BitmapOr? Multi-column GiST indexes can be much more useful, and so btree_gist is useful to enable things like an index over (int, int8range). Another possible use for btree_gin is to enable use of the fastupdate mechanism for indexes on scalars, to speed up bulk insertion but without having to drop the index. I've never demonstrated a realistic benefit there, but I haven't tried very hard recently (last time I really tried was before gin_clean_pending_list and gin_pending_list_limit were added). The "real" solution here is something like log-structured merge trees or fractal indexes, but fastupdate is already here. > However, now that GIN can compress multiple index entries for the same > component value (which has only been true since 9.4, whereas btree_gin > is very much older than that) it seems like it does make sense to use > btree_gin on its own for low-cardinality non-subdividable columns. > And that means that we ought to consider non-subdividable columns as > fully legitimate, not just a weird corner usage. So in particular > I wonder whether it would be worth adding the scaffolding necessary > to support index-only scan when the GIN opclass is one that doesn't > subdivide the data values. > I wouldn't object to that, it just doesn't seem all that exciting. But isn't there some aspiration towards making a next generation of B-Tree index which will also use TID list compression, making them more compact without resorting to GIN? > That leaves me quibbling with some points in Jeff's otherwise excellent > reply: > > > For single column using a btree_gin operator, each index entry holds the > > entire data value. But the system doesn't know about that in any useful > > way, so it doesn't implement index only scans, other than in the special > > case where the value does not matter, like a 'count(*)'. Conceptually > > perhaps this could be fixed, but I don't see it happening. Since an > > index-only scan is usually not much good with only a single-column > index, I > > don't see much excitement to improve things here. > > I'm confused by this; surely IOS is useful even with a single-column > index? Avoiding trips to the heap is always helpful. > But how realistic are the use cases? My thinking was that an IOS for: select bid from pgbench_accounts where bid=5; would be nice if you needed to run that query, but we already know it is 5 for each row where it is 5 so we could just do the count instead of looking at a long column of identical values. Maybe it would be useful in joins or something where we can't rewrite them ourselves, and the planner can't/won't use the transitive law either. It could be useful for disjunction in the same column, or inequality. (Or BETWEEN if we fix the issue you mentioned below). select bid, count(*) from pgbench_accounts where bid = 5 or bid = 7 group by bid; If it can be made to support IOS, perhaps it could also be made to support ORDER BY? > GIN indexes over btree_gin operators do not support inequality or BETWEEN > > queries efficiently. > > Are you sure about that? It's set up to use the "partial match" logic, > which is certainly pretty weird, but it does have the potential for > handling inequalities efficiently. [ poke
Fwd: SSL Error: Certificate verify fail
Hi, I have completed the steps at https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES Specifically: To create a server certificate whose identity can be validated by clients, first create a certificate signing request (CSR) and a public/private key file: openssl req -new -nodes -text -out root.csr \ -keyout root.key -subj "/CN=root.yourdomain.com" chmod og-rwx root.key Then, sign the request with the key to create a root certificate authority (using the default OpenSSL configuration file location on Linux): openssl x509 -req -in root.csr -text -days 3650 \ -extfile /etc/ssl/openssl.cnf -extensions v3_ca \ -signkey root.key -out root.crt Finally, create a server certificate signed by the new root certificate authority: openssl req -new -nodes -text -out server.csr \ -keyout server.key -subj "/CN=dbhost.yourdomain.com" chmod og-rwx server.key openssl x509 -req -in server.csr -text -days 365 \ -CA root.crt -CAkey root.key -CAcreateserial \ -out server.crt However when I issue cmd (this is a generalised version): C:\Program Files (x86)\pgAdmin 4\v4\runtime>psql "sslmode=verify-ca host= host.com dbname=db user=dbuser" It returns: psql: SSL error: certificate verify failed Can anyone advise on what I should try next?
Re: csvlog Behavior when log file missing
Lesley Kimmel wrote: > I have a system where postgres seems to be randomly dying. When researching > the issue > I, of course, began looking at logs. > > I found that much of the logs were missing due to logrotate configuration on > the server. > Logs were being rotated by size and then compressed. Because of this, the > first rotation > would take the log file out from underneath the database and it is not > recreated. > > My suspicion is that the database eventually crashes due to inability to log. > The documentation says the logging_collector will halt other processes if it > is > unable to log. However, it seems that the system continues to run fine for > several > days before crashing and applications using the database continue to operate. > > Does the logrotate configuration possibly explain the behavior? I can add the > 'copytruncate' option to the logrotate configuration and see if the issue > resolves, > but I'd like to also understand the issue. Also, if I add the 'stderr' > destination > and continue to rotate the csv log file out, might I catch errors related to > the > missing csv log or would the logging_collector simply continue operating > since it > still has access to the stderr log? To diagnose the problem, why don't you disable logrotate for a while? Then you can see if it is part of the problem, but above all nothing will mess with your log files and you should be able to find out more about the problem. I never use logrotate with PostgreSQL, but set "log_filename" to "postgresql-%a.log" or "postgresql-%d.log", "log_truncate_on_rotation" to "on" and "log_rotation_size" to "0". Then PostgreSQL rotates the log by itself. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
