Re: Postgres Wal Full
On Thu, Mar 3, 2022 at 2:17 PM pgdba pgdba wrote: > > Hi, thanks your feedback but not have same error in my log file. I am using > streaming replication and have 1 master and 2 slave nodes in async. master > node different size file wal from slave nodes such as; > [root@master1 pg_wal]# ls | wc -l > 6400 > [root@slave2 pg_wal]# ls | wc -l > 332 > [root@slave3 pg_wal]# ls | wc -l > 344 > > wal_keep_segments = 512 > max_wal_size = 2GB > min_wal_size = 1GB > data size : 290G > cpu:8 > ram:16 Few things to check when WAL file grows: 1) archive failures 2) inactive replication slots (use select * from pg_replication_slots;) 3) infrequent checkpoints (use select * from pg_control_checkpoint; to know the last checkpoint time)4) high write (WAL-generating) workloads. Regards, Bharath Rupireddy.
Re: primary_conninfo and restore_command ?
On Thu, Mar 10, 2022 at 7:22 PM Luca Ferrari wrote: > > Hi all, > a friend of mine has shown to me a "strange" configuration of its > physical replication server (13): he has both primary_conninfo and > primary_slot_name, with replication slots active when queried on the > master. So far so good, but in the configuration he has also > restore_command to restore archived WALs from a centralized location. > Does this make sense? > Because if the replica cannot connect to the master, it will not start > at all (so I guess no restore_command will be executed). On the other > hand if the replica can connect to the primary the WALs will be > shipped by means of streaming. > Am I missing something? Yes, restore_command gets executed even in standby mode when walreceiver is unable to receive wal from primary i.e. primary stopped sending WAL, see some comments on it [1]. It looks like restore_command in standby mode isn't mandatory, but is advisable I guess. I hope that clarifies your question. [1] WaitForWALToBecomeAvailable: * * If standby mode is turned off while reading WAL from stream, we move * to XLOG_FROM_ARCHIVE and reset lastSourceFailed, to force fetching * the files (which would be required at end of recovery, e.g., timeline * history file) from archive or pg_wal. We don't need to kill WAL receiver * here because it's already stopped when standby mode is turned off at * the end of recovery. *--- */ case XLOG_FROM_STREAM: /* * Failure while streaming. Most likely, we got here * because streaming replication was terminated, or * promotion was triggered. But we also get here if we * find an invalid record in the WAL streamed from master, * in which case something is seriously wrong. There's * little chance that the problem will just go away, but * PANIC is not good for availability either, especially * in hot standby mode. So, we treat that the same as * disconnection, and retry from archive/pg_wal again. The * WAL in the archive should be identical to what was * streamed, so it's unlikely that it helps, but one can * hope... */ Regards, Bharath Rupireddy.
Re: Set timeout just on a query?
On Tue, May 10, 2022 at 6:29 AM Shaozhong SHI wrote: > > > Can timeout be set just on a query? > > Can we do the following? > > Begin > > do a query > set timeout > Exception > report a record > > End; Won't the statement_timeout [1] help here? [1] https://www.postgresql.org/docs/devel/runtime-config-client.html Regards, Bharath Rupireddy.
Re: Query on postgres_fdw extension
On Thu, May 13, 2021 at 4:58 PM Swathi P wrote:
>
> Hello EveryOne,
>
> Hope you are all doing well and staying safe.
>
> Am Swathi, have been working with postgres for last 3 years. Currently we are
> working on a project to build a sharding solution with the help of native
> postgres_fdw extension. During this process, we have encountered an issue
> with postgres_fdw. I tried to give as much as details below on the issue we
> are facing, it would be of great help if you can help us overcome this issue.
>
> - We have Host_A and Host_B , where Host_A is out coordinator node and Host_B
> is used as our data node.
>
> - Host_B has a table "Table_B" with a sequence id column which auto generates
> the series by default
> CREATE TABLE public.table_a
> (
> id bigint NOT NULL DEFAULT nextval('table_a_id_seq'::regclass),
> topic character varying(50) NOT NULL,
> CONSTRAINT table_a_pk PRIMARY KEY (id)
> )
>
> - on Host_A we have a foreign table created with the ddl below
> CREATE FOREIGN TABLE public.table_a
> (
> id bigint ,
> topic character varying(50) NOT NULL,
> ) SERVER Host_A OPTIONS (schema_name 'public', table_name 'table_a');
>
> - When we try to insert data directly on the table_a while connected to
> Host_B, works fine with the auto incremented values for the id column
>
> - But the same insert fails when run from the coordinator node with below
> error.
> poc=> insert into table_a(topic) values ('test');
> ERROR: null value in column "id" of relation "table_a" violates not-null
> constraint
> DETAIL: Failing row contains (null, test).
> CONTEXT: remote SQL command: INSERT INTO public.table_a(id, topic) VALUES
> ($1, $2)
>
> - If we omit the primary key and not null constraints on the table_a on
> remote host (Host_B) , inserts will work fine on Host_A but a NULL value
> insertedd for the id column instead of sequence
>
> We are looking for some help on understanding the issue here and the best
> possible workaround for the same.
>
> Your help will be greatly appreciated
I think you need to declare your foreign table column id as "serial"
type instead of "bigint". Below is what I tried from my end.
On remote server:
CREATE USER foreign_user;
DROP TABLE table_a;
CREATE TABLE table_a
(
id serial NOT NULL,
topic character varying(50) NOT NULL,
CONSTRAINT table_a_pk PRIMARY KEY (id)
);
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO foreign_user;
On local server:
DROP EXTENSION postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5433', dbname 'postgres');
CREATE USER MAPPING FOR public
SERVER foreign_server
OPTIONS (user 'foreign_user', password '');
CREATE FOREIGN TABLE table_a (id serial NOT NULL,
topic character varying(50) NOT NULL)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'table_a');
SELECT * FROM table_a;
INSERT INTO table_a(topic) VALUES('row1');
INSERT INTO table_a(topic) VALUES('row2');
INSERT INTO table_a(topic) VALUES('row3');
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com
Re: Query on postgres_fdw extension
On Fri, May 14, 2021 at 12:37 PM Swathi P wrote: > > Thanks Bharath for the details. > > In our sharding solution, we have multiple coodinator nodes. If we declare > the table column as serial data type, we might end up having duplicate values > for id column in the table_a in host_b (data node) as cconnections come from > multiple coordinatoor nodes and might end up in duplicate key violations. > > Hence we decided to have the coordinator nodes as stateless and hence > declared the column with no serial/sequence. Let me know if this makes sense. Hm. > Have come across multiple articles on the same issue, i would like to know if > we are doing something wrong here or we have bette workaround for this issue, > > https://www.postgresql.org/message-id/CAP=oouH=FccW4V2zm1VjGit=nzdcxzu2tyboze88v3mxrea...@mail.gmail.com > https://stackoverflow.com/questions/66582694/how-to-get-the-generated-id-from-an-insert-to-a-postgres-foreign-table > https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ Did you try using the approach specified by Michael in [1]? Won't that help? "As a conclusion, you can create tables using unique values across multiple nodes by associating for example foreign_seq_nextval() with DEFAULT for a column. =# CREATE TABLE tab (a int DEFAULT foreign_seq_nextval()); CREATE TABLE =# INSERT INTO tab VALUES (DEFAULT), (DEFAULT), (DEFAULT); INSERT 0 3 =# SELECT * FROM tab; a 9 10 11 (3 rows) " [1] - https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
Re: Query on postgres_fdw extension
On Fri, May 14, 2021 at 8:23 PM Tom Lane wrote: > > [ removing -bugs ] > > Swathi P writes: > > Hence we decided to have the coordinator nodes as stateless and hence > > declared the column with no serial/sequence. Let me know if this makes > > sense. > > Attaching serial-sequence defaults on both sides would certainly not > work very well, because the sequences wouldn't stay in sync. > > Unfortunately, postgres_fdw just doesn't have a good way right now > to make use of dynamically-generated defaults at the remote server. > If you leave out a column in your INSERT, it's going to compute > and send the locally-defined default (which is just null in this > case), so the remote's default expression is never used. > > I remember that we spent a great deal of effort in postgres_fdw's > early days, trying to find a way that we could use the remote's > defaults in cases like this. But everything we tried ended up > causing horrible semantic inconsistencies, so we ended up with > the always-use-the-local-default approach. There was some feeling > that maybe this could be revisited later, but no one's done so. > > One conceivable workaround is to do your insertions through a > foreign table that doesn't even have the serial column, so that > the INSERT command received by the remote server lacks that > column and the default gets applied. Probably too messy though. Then the serial column cannot be selected via the foreign table. Maybe, drop the serial column from the foreign table before insertions and add the serial column before the selects from the foreign table. This is not elegant though. With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
Re: Query on postgres_fdw extension
On Tue, Jun 1, 2021 at 3:31 PM Etsuro Fujita wrote: > > Hi, > > On Fri, May 14, 2021 at 6:08 PM Swathi P wrote: > > In our sharding solution, we have multiple coodinator nodes. If we declare > > the table column as serial data type, we might end up having duplicate > > values for id column in the table_a in host_b (data node) as cconnections > > come from multiple coordinatoor nodes and might end up in duplicate key > > violations. > > > > Hence we decided to have the coordinator nodes as stateless and hence > > declared the column with no serial/sequence. Let me know if this makes > > sense. > > It seems reasonable to me to make coodinator nodes stateless, but may > I ask the reason you use multiple coordinator nodes? Perhaps, as a redundant node to avoid single point of failures? It's just a guess as I'm not the right one to answer that question though. With Regards, Bharath Rupireddy.
Re: max_connections different between primary and standby: is it possible?
On Thu, Feb 3, 2022 at 3:17 PM Julien Rouhaud wrote: > > Hi, > > On Thu, Feb 03, 2022 at 10:36:37AM +0100, Luca Ferrari wrote: > > Hi all, > > running PostgreSQL 14, physical replication with slot, after changing > > (increasing) the max_connections on the primary, I had this message at > > a restart from the standby: > > > > DETAIL: max_connections = 100 is a lower setting than on the primary > > server, where its value was 300. > > > > and the standby does not start until I raise also its max_connections. > > Why is PostgreSQL requiring the max_connections to be aligned between > > the primary and the standby? > > The value needs to be at least equal as the value on the primary node, but it > can be bigger. > > That's because the standby needs to have enough resources to replay the > activity from the primary, including some heavyweight locks acquisition, and > the number of locks you can hold at one time is partially based on > max_connections. Agree that the standby should atleast have the capacity that the primary has in terms of resources. But what I don't like about that code is calling RecoveryRequiresIntParameter for each parameter separately and crashing the server FATALly for each insufficient parameter. Imagine if all the parameters were set to insufficient values on the standby and users keep setting the reported parameter to the right value and restart the server. At max, 5 FATAL failure-set right value-restart have to be performed. Instead, it would be better if the server emits a single log with all the insufficient parameters(max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions and max_locks_per_transaction) values and crashes FATALly. The users can look at the logs at once, set all the insufficient parameters to right values and restart the server. Regards, Bharath Rupireddy.
