Re: Minor fixes for PostgreSQL 13 documentation
On Sun, Aug 23, 2020 at 09:11:58PM +0900, Michael Paquier wrote: > I have looked again at the documentation and found just the five > places mentioned previously, giving the attached. And fixed with 77c1537, so we are done here. -- Michael signature.asc Description: PGP signature
Re: Create a Foreign Table for PostgreSQL CSV Logs
There must not be constraints at all. Constraints are needed to check incoming data to the table. But here table is read-only for database! So all your constraints is totally useless. > > could become: > > > > connection_from text check(connection_from ~ '^[^:]+:[0-9]+$) -- the host > > and > > port of the client, colon-separated > David J.
Re: Document "59.2. Built-in Operator Classes" have a clerical error?
On Sat, Aug 22, 2020 at 02:59:02PM -0400, Alvaro Herrera wrote: > On 2020-Aug-22, Tom Lane wrote: > > > If you don't want to go all the way and list the operators with their > > input types, maybe we should just do what the OP thought was correct > > and delete the duplicate operator names. It's already the case that > > the table isn't telling you exactly which input types the operators > > accept, so why not be a little bit fuzzier? > > Well, if we're going to have a table, let's have a useful table. What's > wrong with using the same contents \dAo shows? It seemed reasonable > enough to me. I don't think it is worth it, plus we would need to adjust the docs if system catalog layout changes. I think we just want something concise and simple, but also accurate. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Create a Foreign Table for PostgreSQL CSV Logs
On Mon, Aug 24, 2020 at 6:17 AM Олег Самойлов wrote: > There must not be constraints at all. Constraints are needed to check > incoming data to the table. But here table is read-only for database! > Please don't top-post. > So all your constraints is totally useless. > > > could become: > > > > > > connection_from text check(connection_from ~ '^[^:]+:[0-9]+$) -- the > host and > > > port of the client, colon-separated > I'll agree that the benefit for adding the constraints to a foreign table are less than for a normal table but it is still not zero. Constraints are also a form of documentation. And also can be used (at least non-null ones) during optimization. David J.
Re: Procedures
On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote: > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian wrote: > > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote: > > > [...] the CALL command. If the CALL command is not part of an explicit > > transaction a procedure can also manage multiple transactions during its > > execution. > > OK, how is this updated patch? > > > Looks good. I felt "begin and commit" was a bit wordy but it works. So, I was worried that "manage multiple transactions" could imply something like savepoints, which can be managed by functions. It is really the top-level begin/commit that is unique for procedures. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Create a Foreign Table for PostgreSQL CSV Logs
On Mon, Aug 24, 2020 at 07:40:49AM -0700, David G. Johnston wrote: > On Mon, Aug 24, 2020 at 6:17 AM Олег Самойлов wrote: > > There must not be constraints at all. Constraints are needed to check > incoming data to the table. But here table is read-only for database! > > > Please don't top-post. > > > > So all your constraints is totally useless. > > > could become: > > > > > > connection_from text check(connection_from ~ '^[^:]+:[0-9]+$) -- the > host and > > > port of the client, colon-separated > > > I'll agree that the benefit for adding the constraints to a foreign table are > less than for a normal table but it is still not zero. Constraints are also a > form of documentation. And also can be used (at least non-null ones) during > optimization. I feel constraints are going to lose focus of what we are trying to show. Do the constraints actually do anything on a foreign table? If not, we would have to mention that here too, which might be fine. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Procedures
po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian napsal: > On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote: > > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian wrote: > > > > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote: > > > > > [...] the CALL command. If the CALL command is not part of an > explicit > > > transaction a procedure can also manage multiple transactions > during its > > > execution. > > > > OK, how is this updated patch? > > > > > > Looks good. I felt "begin and commit" was a bit wordy but it works. > > So, I was worried that "manage multiple transactions" could imply > something like savepoints, which can be managed by functions. It is > really the top-level begin/commit that is unique for procedures. > Functions is executed under outer transaction every time - rollback to save point hasn't impact on outer transaction. Inside procedures (in special case) can be transactions ended (by statements COMMIT or ROLLBACK). Immediately is started new transaction. > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > > > >
Re: Procedures
On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote: > > > po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian napsal: > > On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote: > > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian wrote: > > > > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote: > > > > > [...] the CALL command. If the CALL command is not part of an > explicit > > > transaction a procedure can also manage multiple transactions > during its > > > execution. > > > > OK, how is this updated patch? > > > > > > Looks good. I felt "begin and commit" was a bit wordy but it works. > > So, I was worried that "manage multiple transactions" could imply > something like savepoints, which can be managed by functions. It is > really the top-level begin/commit that is unique for procedures. > > Functions is executed under outer transaction every time - rollback to save > point hasn't impact on outer transaction. Inside procedures (in special case) > can be transactions ended (by statements COMMIT or ROLLBACK). Immediately is > started new transaction. Well, savepoints control what commands are considered _part_ of the outer transaction, so in a way you are managing what is in the outer transaction. This is why begin/commit was clearer for me. Maybe "start and commit" is clearer? -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Create a Foreign Table for PostgreSQL CSV Logs
> 24 авг. 2020 г., в 18:07, Bruce Momjian написал(а):
>
> On Mon, Aug 24, 2020 at 07:40:49AM -0700, David G. Johnston wrote:
>> On Mon, Aug 24, 2020 at 6:17 AM Олег Самойлов wrote:
>>
>>There must not be constraints at all. Constraints are needed to check
>>incoming data to the table. But here table is read-only for database!
>>
>>
>> Please don't top-post.
>>
>>
>>
>>So all your constraints is totally useless.
could become:
connection_from text check(connection_from ~ '^[^:]+:[0-9]+$) -- the
>>host and
port of the client, colon-separated
>>
>>
>> I'll agree that the benefit for adding the constraints to a foreign table are
>> less than for a normal table but it is still not zero. Constraints are also
>> a
>> form of documentation. And also can be used (at least non-null ones) during
>> optimization.
>
> I feel constraints are going to lose focus of what we are trying to
> show. Do the constraints actually do anything on a foreign table?
I'll add: "on foreign table based on read only text file without indexes, etc".
:) But in this case I indeed added some CHECK() constraints for old PostgreSQL
and change them for the partition syntax sugar on new PostgreSQL to point on
different files according to PostgreSQL default log config, where logs of each
day of a week is kept in the different files. For example:
ALTER SYSTEM SET log_destination=csvlog;
SELECT pg_reload_conf();
CREATE EXTENSION file_fdw;
CREATE SERVER file_fdw FOREIGN DATA WRAPPER file_fdw;
BEGIN;
CREATE SCHEMA pglog;
CREATE TABLE pglog.pglog (
log_time timestamp(3),
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) PARTITION BY LIST (date_part('isodow', log_time));
CREATE FOREIGN TABLE pglog.Mon
PARTITION OF pglog.pglog FOR VALUES IN (1)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Mon.csv',
format 'csv' );
CREATE FOREIGN TABLE pglog.Tue
PARTITION OF pglog.pglog FOR VALUES IN (2)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Tue.csv',
format 'csv' );
CREATE FOREIGN TABLE pglog.Wed
PARTITION OF pglog.pglog FOR VALUES IN (3)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Wed.csv',
format 'csv' );
CREATE FOREIGN TABLE pglog.Thu
PARTITION OF pglog.pglog FOR VALUES IN (4)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Thu.csv',
format 'csv' );
CREATE FOREIGN TABLE pglog.Fri
PARTITION OF pglog.pglog FOR VALUES IN (5)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Fri.csv',
format 'csv' );
CREATE FOREIGN TABLE pglog.Sat
PARTITION OF pglog.pglog FOR VALUES IN (6)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Sat.csv',
format 'csv' );
CREATE FOREIGN TABLE pglog.Sun
PARTITION OF pglog.pglog FOR VALUES IN (7)
SERVER file_fdw
OPTIONS ( filename '/var/lib/pgsql/12/data/log/postgresql-Sun.csv',
format 'csv' );
COMMIT;
But there is another point of view. The same table schema used now not only for
file_fdw access to the log, but also in example where this is an ordinary
table. In this case may be some other optimisation: indexes, etc.
But do we really need in the simple example such detailed and specific code?
May be better give as simple as possible example as example, which everyone
will can adapt for his own needs.
Re: Procedures
On Mon, Aug 24, 2020 at 11:35:57AM -0400, Bruce Momjian wrote: > On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote: > > > > > > po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian napsal: > > > > On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote: > > > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian > > wrote: > > > > > > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote: > > > > > > > [...] the CALL command. If the CALL command is not part of an > > explicit > > > > transaction a procedure can also manage multiple transactions > > during its > > > > execution. > > > > > > OK, how is this updated patch? > > > > > > > > > Looks good. I felt "begin and commit" was a bit wordy but it works. > > > > So, I was worried that "manage multiple transactions" could imply > > something like savepoints, which can be managed by functions. It is > > really the top-level begin/commit that is unique for procedures. > > > > Functions is executed under outer transaction every time - rollback to save > > point hasn't impact on outer transaction. Inside procedures (in special > > case) > > can be transactions ended (by statements COMMIT or ROLLBACK). Immediately is > > started new transaction. > > Well, savepoints control what commands are considered _part_ of the > outer transaction, so in a way you are managing what is in the outer > transaction. This is why begin/commit was clearer for me. Maybe "start > and commit" is clearer? Should the new text be? a procedure can commit and begin new transactions during its execution. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Procedures
po 24. 8. 2020 v 17:38 odesílatel Bruce Momjian napsal: > On Mon, Aug 24, 2020 at 11:35:57AM -0400, Bruce Momjian wrote: > > On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote: > > > > > > > > > po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian > napsal: > > > > > > On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote: > > > > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian > wrote: > > > > > > > > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston > wrote: > > > > > > > > > [...] the CALL command. If the CALL command is not part > of an > > > explicit > > > > > transaction a procedure can also manage multiple > transactions > > > during its > > > > > execution. > > > > > > > > OK, how is this updated patch? > > > > > > > > > > > > Looks good. I felt "begin and commit" was a bit wordy but it > works. > > > > > > So, I was worried that "manage multiple transactions" could imply > > > something like savepoints, which can be managed by functions. It > is > > > really the top-level begin/commit that is unique for procedures. > > > > > > Functions is executed under outer transaction every time - rollback to > save > > > point hasn't impact on outer transaction. Inside procedures (in > special case) > > > can be transactions ended (by statements COMMIT or ROLLBACK). > Immediately is > > > started new transaction. > > > > Well, savepoints control what commands are considered _part_ of the > > outer transaction, so in a way you are managing what is in the outer > > transaction. This is why begin/commit was clearer for me. Maybe "start > > and commit" is clearer? > > Should the new text be? > > a procedure can commit and begin new transactions during its > execution. > sure. Maybe enhancing about sentence like "it is not possible in a function." and "a procedure can commit (or rollback) and begin new transactions during its execution" > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > >
Re: Document "59.2. Built-in Operator Classes" have a clerical error?
On 2020-Aug-24, Bruce Momjian wrote: > I don't think it is worth it, plus we would need to adjust the docs if > system catalog layout changes. I think we just want something concise > and simple, but also accurate. I argued for \dAo, not straight catalog output -- that was a straw man. I can't produce the docbook right now but I volunteer to show a screenshot for what I propose later this week. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Procedures
On Mon, Aug 24, 2020 at 05:51:29PM +0200, Pavel Stehule wrote: > sure. Maybe enhancing about sentence like "it is not possible in a function." > > and > > "a procedure can commit (or rollback) and begin new transactions during its > execution" OK, updated patch. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 6de464c654..5dda2a80af 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -84,8 +84,11 @@ A procedure is a database object similar to a function. The difference is that a procedure does not return a value, so there is no return type declaration. While a function is called as part of a query or DML -command, a procedure is called explicitly using -the statement. +command, a procedure is called in isolation using +the command. If the CALL command is not +part of an explicit transaction, a procedure can commit, rollback, +and begin new transactions during its execution, which is not possible +in functions.
Re: Document "59.2. Built-in Operator Classes" have a clerical error?
On Mon, Aug 24, 2020 at 11:59:22AM -0400, Alvaro Herrera wrote: > On 2020-Aug-24, Bruce Momjian wrote: > > > I don't think it is worth it, plus we would need to adjust the docs if > > system catalog layout changes. I think we just want something concise > > and simple, but also accurate. > > I argued for \dAo, not straight catalog output -- that was a straw man. > > I can't produce the docbook right now but I volunteer to show a > screenshot for what I propose later this week. Sure, I can wait. Is this the only place where it would make sense? -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: Procedures
po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian napsal: > On Mon, Aug 24, 2020 at 05:51:29PM +0200, Pavel Stehule wrote: > > sure. Maybe enhancing about sentence like "it is not possible in a > function." > > > > and > > > > "a procedure can commit (or rollback) and begin new transactions during > its > > execution" > > OK, updated patch. > it is clean for me > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > >
Re: Procedures
On Mon, Aug 24, 2020 at 9:09 AM Pavel Stehule wrote: > > > po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian napsal: > >> On Mon, Aug 24, 2020 at 05:51:29PM +0200, Pavel Stehule wrote: >> > sure. Maybe enhancing about sentence like "it is not possible in a >> function." >> > >> > and >> > >> > "a procedure can commit (or rollback) and begin new transactions during >> its >> > execution" >> >> OK, updated patch. >> > > it is clean for me > Works for me. I wasn't thinking about the implication of the wording with respect to Savepoints in functions. David J.
Openssl v3_ca
A few years ago I figured out how to create intermediate certificates that are transferred across OpenSSL connections by using the v3_ca extension, and added this to the PG documentation. I have now just figured out that v3_ca is just a heading in the openssl configuration file, e.g., /etc/ssl/openssl.cnf, and that it is specifically this line that enables this to work: basicConstraints = critical,CA:true I have created the attached documentation patch to clarify exactly what is needed, in case non-openssl tools are used. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index c8698898f3..f705c4fec1 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -2194,7 +2194,8 @@ pg_dumpall -p 5432 | psql -d postgres -p 5433 can also be appended to the file. Doing this avoids the necessity of storing intermediate certificates on clients, assuming the root and intermediate certificates were created with v3_ca - extensions. This allows easier expiration of intermediate certificates. + extensions (which sets CA:TRUE on certificates). + This allows easier expiration of intermediate certificates.
PostgreSQL switchover process
Hi All, I'm trying to do the Switchover process in postgres. 1) I have my primary cluster running on port 5432 on server A 2) secondary cluster on the port 5432 on server B I did setup streaming replication b/w primary and secondary using replication slots. I did the following steps to perform switchover 1) ran a checkpoint on primary. 2) verified secondary status and it is up to date with primary. 3) stopped primary using pg_ctl -mf i.e force. 4) promoted primary using trigger file and verified the status of the new primary; it is not in recovery mode and running on new timeline ID 2. 5) I did create the recovery.conf file on old primary i.e new standby with primaray_coninfo: new primary(B) recovery_target_timeline='latest' primary_slot_name: "slot I created in new_standby" 6) I started the new standby. It is up and running but it is not in recovery mode. But the wal sender process on primary and receiver process on secondary are not running. i.e it looks like my replication is broken. what steps am I missing here without rebuilding a new standby i.e old primary I want the replication happen b/w my new primary and secondary. Could you suggest any good documentation? thanks in advance. Thanks, Veeru.
Re: Openssl v3_ca
Greetings, * Bruce Momjian (br...@momjian.us) wrote: > A few years ago I figured out how to create intermediate certificates > that are transferred across OpenSSL connections by using the v3_ca > extension, and added this to the PG documentation. > > I have now just figured out that v3_ca is just a heading in the openssl > configuration file, e.g., /etc/ssl/openssl.cnf, and that it is > specifically this line that enables this to work: > > basicConstraints = critical,CA:true Yes, v3_ca refers to a stanza in the default openssl config. > I have created the attached documentation patch to clarify exactly what > is needed, in case non-openssl tools are used. > diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml > index c8698898f3..f705c4fec1 100644 > --- a/doc/src/sgml/runtime.sgml > +++ b/doc/src/sgml/runtime.sgml > @@ -2194,7 +2194,8 @@ pg_dumpall -p 5432 | psql -d postgres -p 5433 > can also be appended to the file. Doing this avoids the necessity of > storing intermediate certificates on clients, assuming the root and > intermediate certificates were created with v3_ca All CAs need to have CA:TRUE set on them, root and intermediate, so the above isn't really correct.. > - extensions. This allows easier expiration of intermediate certificates. > + extensions (which sets CA:TRUE on certificates). Probably better would be to specifically say "This sets 'ca' to 'true' for the basic constraints of the certificate." or similar language. Simply saying "CA:TRUE" doesn't seem to really be an improvement over just referencing the v3_ca stanza. > + This allows easier expiration of intermediate certificates. While true, there's certainly other reasons why someone might want to run intermediate CAs.. I'm not sure that we really need to go into the discussion about why they make sense to have. Thanks, Stephen signature.asc Description: PGP signature
Re: Openssl v3_ca
On Mon, Aug 24, 2020 at 04:00:09PM -0400, Stephen Frost wrote: > Greetings, > > * Bruce Momjian (br...@momjian.us) wrote: > > A few years ago I figured out how to create intermediate certificates > > that are transferred across OpenSSL connections by using the v3_ca > > extension, and added this to the PG documentation. > > > > I have now just figured out that v3_ca is just a heading in the openssl > > configuration file, e.g., /etc/ssl/openssl.cnf, and that it is > > specifically this line that enables this to work: > > > > basicConstraints = critical,CA:true > > Yes, v3_ca refers to a stanza in the default openssl config. > > > I have created the attached documentation patch to clarify exactly what > > is needed, in case non-openssl tools are used. > > > diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml > > index c8698898f3..f705c4fec1 100644 > > --- a/doc/src/sgml/runtime.sgml > > +++ b/doc/src/sgml/runtime.sgml > > @@ -2194,7 +2194,8 @@ pg_dumpall -p 5432 | psql -d postgres -p 5433 > > can also be appended to the file. Doing this avoids the necessity of > > storing intermediate certificates on clients, assuming the root and > > intermediate certificates were created with v3_ca > > All CAs need to have CA:TRUE set on them, root and intermediate, so the > above isn't really correct.. Uh, I think you can get away, at least in Postgres, of not setting CA:true for root certificates. In fact, you can even skip it for intermediates if you make sure the intermediate cert is on both sides of the SSL connection. I found this v3_ca because some people were saying you had to have the intermediates on both sides, and others said it would be shipped, and I was unclear why it only worked some of the time. v3_ca was the answer to having them shipped. > > - extensions. This allows easier expiration of intermediate certificates. > > + extensions (which sets CA:TRUE on certificates). > > Probably better would be to specifically say "This sets 'ca' to 'true' > for the basic constraints of the certificate." or similar language. > Simply saying "CA:TRUE" doesn't seem to really be an improvement over > just referencing the v3_ca stanza. Good, I was unclear what wording to use; updated patch attached. > > + This allows easier expiration of intermediate certificates. > > While true, there's certainly other reasons why someone might want to > run intermediate CAs.. I'm not sure that we really need to go into the > discussion about why they make sense to have. Agreed. If we wanted to get into that, we would need to make a new doc section about it. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index c8698898f3..a01add94b7 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -2193,8 +2193,10 @@ pg_dumpall -p 5432 | psql -d postgres -p 5433 The certificates of intermediate certificate authorities can also be appended to the file. Doing this avoids the necessity of storing intermediate certificates on clients, assuming the root and - intermediate certificates were created with v3_ca - extensions. This allows easier expiration of intermediate certificates. + intermediate certificates were created with v3_ca + extensions. (This sets the certificate's basic constraint of + CA to true.) + This allows easier expiration of intermediate certificates.
[no subject]
Hi please release me from this group
Re: your mail
On Tue, Aug 25, 2020 at 06:21:28AM +0530, mahammad shoyab wrote: > Hi please release me from this group It is possible to manage your subscriptions to the PostgreSQL community mailing lists using your community account. Please see here: https://lists.postgresql.org/manage/ -- Michael signature.asc Description: PGP signature
Re: PostgreSQL switchover process
On Mon, Aug 24, 2020 at 03:56:43PM -0400, rams nalabolu wrote: > I did the following steps to perform switchover > 1) ran a checkpoint on primary. > 2) verified secondary status and it is up to date with primary. > 3) stopped primary using pg_ctl -mf i.e force. This is not a force mode, but the fast mode, where all existing connections are forcibly stopped, and that the shutdown is clean, with a shutdown checkpoint generated before finishing the shutdown sequence. During this shutdown, the primary makes sure that all standbys have flushed WAL up to the point of the shutdown checkpoint. Note that this makes rather unnecessary the checkpoint you ran on the primary in step 1. > 4) promoted primary using trigger file and verified the status of the new > primary; it is not in recovery mode and running on new timeline ID 2. I think that you mean promotion of the standby here. > 5) I did create the recovery.conf file on old primary i.e new standby with > primaray_coninfo: new primary(B) > recovery_target_timeline='latest' > primary_slot_name: "slot I created in new_standby" Typo here. You mean primary_conninfo. > 6) I started the new standby. It is up and running but it is not in > recovery mode. This switchover flow is a good base, so it should be possible to reuse your previous primary as a standby. > But the wal sender process on primary and receiver process on secondary are > not running. i.e it looks like my replication is broken. > what steps am I missing here without rebuilding a new standby i.e old > primary I want the replication happen b/w my new primary and > secondary. It may be many things without more information. Could you check pg_stat_replication on the primary and pg_stat_wal_receiver on the standby? Most likely something is wrong with primary_conninfo, but the logs of the standby should have enough information to let you know what happened. Another thing you are not telling is the version of PostgreSQL you are using here. In 12 and newer versions, support for recovery.conf has been removed, requiring roughly the creation of standby.signal with all recovery parameters set in postgresql.conf if you want to set up a standby. -- Michael signature.asc Description: PGP signature
Re: PostgreSQL switchover process
On 2020/08/25 11:22, Michael Paquier wrote: On Mon, Aug 24, 2020 at 03:56:43PM -0400, rams nalabolu wrote: I did the following steps to perform switchover 1) ran a checkpoint on primary. 2) verified secondary status and it is up to date with primary. 3) stopped primary using pg_ctl -mf i.e force. This is not a force mode, but the fast mode, where all existing connections are forcibly stopped, and that the shutdown is clean, with a shutdown checkpoint generated before finishing the shutdown sequence. During this shutdown, the primary makes sure that all standbys have flushed WAL up to the point of the shutdown checkpoint. Note that this makes rather unnecessary the checkpoint you ran on the primary in step 1. 4) promoted primary using trigger file and verified the status of the new primary; it is not in recovery mode and running on new timeline ID 2. I think that you mean promotion of the standby here. 5) I did create the recovery.conf file on old primary i.e new standby with primaray_coninfo: new primary(B) recovery_target_timeline='latest' primary_slot_name: "slot I created in new_standby" Did you enable standby_mode? Typo here. You mean primary_conninfo. 6) I started the new standby. It is up and running but it is not in recovery mode. This switchover flow is a good base, so it should be possible to reuse your previous primary as a standby. But the wal sender process on primary and receiver process on secondary are not running. i.e it looks like my replication is broken. what steps am I missing here without rebuilding a new standby i.e old primary I want the replication happen b/w my new primary and secondary. It may be many things without more information. Could you check pg_stat_replication on the primary and pg_stat_wal_receiver on the standby? Most likely something is wrong with primary_conninfo, but the logs of the standby should have enough information to let you know what happened. Another thing you are not telling is the version of PostgreSQL you are using here. In 12 and newer versions, support for recovery.conf has been removed, requiring roughly the creation of standby.signal with all recovery parameters set in postgresql.conf if you want to set up a standby. -- Michael -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
Re: PostgreSQL switchover process
Yes, I did. On Mon, Aug 24, 2020 at 10:43 PM Fujii Masao wrote: > > > On 2020/08/25 11:22, Michael Paquier wrote: > > On Mon, Aug 24, 2020 at 03:56:43PM -0400, rams nalabolu wrote: > >> I did the following steps to perform switchover > >> 1) ran a checkpoint on primary. > >> 2) verified secondary status and it is up to date with primary. > >> 3) stopped primary using pg_ctl -mf i.e force. > > > > This is not a force mode, but the fast mode, where all existing > > connections are forcibly stopped, and that the shutdown is clean, with > > a shutdown checkpoint generated before finishing the shutdown > > sequence. During this shutdown, the primary makes sure that all > > standbys have flushed WAL up to the point of the shutdown checkpoint. > > Note that this makes rather unnecessary the checkpoint you ran on the > > primary in step 1. > > > >> 4) promoted primary using trigger file and verified the status of the > new > >> primary; it is not in recovery mode and running on new timeline ID 2. > > > > I think that you mean promotion of the standby here. > > > >> 5) I did create the recovery.conf file on old primary i.e new standby > with > >>primaray_coninfo: new primary(B) > >>recovery_target_timeline='latest' > >>primary_slot_name: "slot I created in new_standby" > > Did you enable standby_mode? > > > > > > Typo here. You mean primary_conninfo. > > > >> 6) I started the new standby. It is up and running but it is not in > >> recovery mode. > > > > This switchover flow is a good base, so it should be possible to reuse > > your previous primary as a standby. > > > >> But the wal sender process on primary and receiver process on secondary > are > >> not running. i.e it looks like my replication is broken. > >> what steps am I missing here without rebuilding a new standby i.e old > >> primary I want the replication happen b/w my new primary and > >> secondary. > > > > It may be many things without more information. Could you check > > pg_stat_replication on the primary and pg_stat_wal_receiver on the > > standby? Most likely something is wrong with primary_conninfo, but > > the logs of the standby should have enough information to let you know > > what happened. Another thing you are not telling is the version of > > PostgreSQL you are using here. In 12 and newer versions, > > support for recovery.conf has been removed, requiring roughly the > > creation of standby.signal with all recovery parameters set in > > postgresql.conf if you want to set up a standby. > > -- > > Michael > > > > -- > Fujii Masao > Advanced Computing Technology Center > Research and Development Headquarters > NTT DATA CORPORATION >
Re: Document "59.2. Built-in Operator Classes" have a clerical error?
On Mon, Aug 24, 2020 at 12:01:00PM -0400, Bruce Momjian wrote: > Sure, I can wait. Is this the only place where it would make sense? I think so. If there are other places, it does not prevent improving what we already know needs improvement. FWIW, the layout I was thinking about is something like the patch attached. I have only patched GIN to give an idea of the shape of the tables. The PNG file attached is a screenshot of the HTML generated. I know that we try to limit the use of morerows, but it seems much better to me to use morerows for those pages here knowing the small size of the tables. We could split that into multiple tables instead, still I find the single-table approach cleaner. -- Michael diff --git a/doc/src/sgml/gin.sgml b/doc/src/sgml/gin.sgml index 2d862669c3..6fee0280be 100644 --- a/doc/src/sgml/gin.sgml +++ b/doc/src/sgml/gin.sgml @@ -85,43 +85,57 @@ - array_ops - anyarray - - && - <@ - = - @> - + array_ops + anyarray + &&(anyarray,anyarray) - jsonb_ops - jsonb - - ? - ?& - ?| - @> - @? - @@ - + @>(anyarray,anyarray) - jsonb_path_ops - jsonb - - @> - @? - @@ - + <@(anyarray,anyarray) - tsvector_ops - tsvector - - @@ - @@@ - + =(anyarray,anyarray) + + + jsonb_ops + jsonb + @>(jsonb,jsonb) + + + @?(jsonb,jsonpath) + + + @@(jsonb,jsonpath) + + + ?(jsonb,text) + + + ?|(jsonb,text[]) + + + ?&(jsonb,text[]) + + + jsonb_path_ops + jsonb + @>(jsonb,jsonb) + + + @?(jsonb,jsonpath) + + + @@(jsonb,jsonpath) + + + tsvector_ops + tsvector + @@(tsvector,tsquery) + + + @@@(tsvector,tsquery) signature.asc Description: PGP signature
