What is the difference in writing test cases and writing Sanity test cases for sign up of any website? Could anyone please let me understand by giving few test cases or example of Sanity test cases for sign up page of any website?

The addition of the on update stanza seems to have fixed this issue. I still have no clue why it stopped triggering on just insert but there you have it.

Yugabytedb is a distributed rdbms. It uses data redundancy. How does it keep a good performance while having to keep multiple copies up to date?

The magic is called "row lock". The updates on the counter column are serialized by row locks, so there cannot be any race condition. A lost update can never happen if you read and write the data in a single UPDATE statement.

I have a 2 node postresql(12.9) cluster with streaming replication enabled. I have also set the archive and restore commands which I verified is working as expected. No replication slots or wal_keep_segments configured.

archive_command ='test ! -f /archive_location/%f && cp %p /archive_location/%f'
restore_command = 'cp /archive_location/%f %p'

In this setup, I notice that if the standby disconnects after receiving a partial WAL file and if the same WAL gets archived and recycled from pg_wal on the primary node, then on reconnection, this standby never catches up with primary.

The error seen on standby is - ERROR:  requested WAL segment 0000000100000000000000XX has already been removed.

Debugging revealed the following -

1- Issue only seen when partial WAL file written on standby at time of disconnection. Since streaming replication is enabled, postgres doesn't wait for the 16MB WAL segment to fill up before shipping WAL to standby. So there could be partially written WAL on standby at any point. Confirmed by file compare of same WAL on primary and standby -

cmp 0000000100000000000000XX /tmp/0000000100000000000000XX
0000000100000000000000XX /tmp/0000000100000000000000XX differ: byte 15722105, line 68077

On reconnection, postgres first tries restoring WAL from archive but on failure, falls on streaming the same WAL from publisher over TCP connection. Since wal_keep_segments is not set, this WAL is recycled from pg_wal on publisher and standby is now stuck in this loop

628ed196.bd8bf 0DEBUG:  00000: switched WAL source from stream to archive after failure
2
628ed196.bd8bf 0DEBUG:  00000: record with incorrect prev-link 1/FDFF2008 at 0/EDEFE678
2022-05-26 06:32:19 IST   776383 628ed196.bd8bf 0LOCATION:  ReadRecord, xlog.c:4348
2022-05-26 06:32:19 IST   776383 628ed196.bd8bf 0DEBUG:  00000: switched WAL source from archive to stream after failure
2022-05-26 06:32:19 IST   776629 628ed19b.bd9b5 0FATAL:  XX000: could not receive data from WAL stream: ERROR:  requested WAL segment 0000000100000000000000XX has already been removed

Given this info looks like wal_keep_segments is needed even when archive + restore is set for streaming replication. Is this true? Am I missing something?

Edit - Adding necessary log info here for improved readability -

628ed196.bd8bf 0DEBUG:  00000: switched WAL source from stream to archive after failure
2022-05-26 06:32:19 IST   776383 628ed196.bd8bf 0LOCATION:  WaitForWALToBecomeAvailable, xlog.c:12208
2022-05-26 06:32:19 IST   776383 628ed196.bd8bf 0DEBUG:  00000: record with incorrect prev-link 1/FDFF2008 at 0/EDEFE678
2022-05-26 06:32:19 IST   776383 628ed196.bd8bf 0LOCATION:  ReadRecord, xlog.c:4348 2022-05-26 06:32:19 IST   776383 628ed196.bd8bf 0DEBUG:  00000: switched WAL source from archive to stream after failure
2022-05-26 06:32:19 IST   776381 628ed196.bd8bd 0LOCATION:  LogChildExit, postmaster.c:3697
2022-05-26 06:32:19 IST   776629 628ed19b.bd9b5 0LOG:  00000: started streaming WAL from primary at 0/ED000000 on timeline 1
2022-05-26 06:32:19 IST   776629 628ed19b.bd9b5 0LOCATION:  WalReceiverMain, walreceiver.c:372 2022-05-26 06:32:19 IST   776629 628ed19b.bd9b5 0FATAL:  XX000: could not receive data from WAL stream: ERROR:  requested WAL segment 0000000100000000000000ED has already been removed

You could use the word similarity operator :

SELECT ... FROM tab
WHERE 'postgres' 

To speed that up, you can create a GIN index on that expression:

CREATE INDEX ON tab USING gin (concat(title, ' ', description) gin_trgm_ops);

You can adjust the parameter pg_trgm.word_similarity_threshold to get the desired sensitivity.

is there a third party tool or a way to automate installing windows updates on windows clusters with sql server high availability groups installed? right now we have a manual process of installing then failing over etc.

I understand that SQL AG supports the automatic page repair when the primary is synchronized with the secondary.

Suppose the AG is configured in an async commit mode, then does also mean that the primary and secondary are synchronized (I know there would be a delay), there by providing for automatic page repair?

When a new password is to be used, you need to restart (=stop and start) the service. See the https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/scm-services-change-the-service-startup-account?view=sql-server-ver15 :

When you change the service startup account for the Database Engine and SQL Server Agent, the SQL Server service (the Database Engine) must be restarted for the change to take effect. When the service is restarted, all databases associated with that instance of SQL Server will be unavailable until the service successfully restarts.

Now, why does changing the password require a restart? That's because when a process starts, it generates a https://docs.microsoft.com/en-us/windows/win32/secauthz/access-tokens . The token's validity depends on its password. If you change the account password on AD and for the service, the current process still keeps using the old password. In a while, that leads to locking the account, as it tires to re-authenticate with the old (=invalid) password.

Oh, and don't change the service account's password https://pages.nist.gov/800-63-FAQ/#q-b06 it's too old. If frequent changes are needed, use virtual or managed accounts instead.

i have function

CREATE FUNCTION create_post(
    txt text,
    created BIGINT,
    photourl text,
    user_id bigint
) RETURNS TABLE(
    id bigint,
    "text" text,
    created_at timestamp,
    photo_url text,
    user_email text,
    comments_count int
) AS $$
DECLARE ret_id bigint;
BEGIN

INSERT INTO posts(

"text",

created_at,

photo_url,

user_id

)

VALUES ($1, to_timestamp($2), $3, $4)

RETURNING posts.id INTO ret_id;
RETURN query

SELECT p.id AS id,

p.text AS "text",

p.created_at AS created_at,

p.photo_url AS photo_url,

u.email AS user_email,

COUNT(c) AS comments_count

FROM posts AS p

INNER JOIN users AS u ON u.id = p.user_id

LEFT JOIN comments AS c ON c.post_id = p.id

WHERE p.id = ret_id

GROUP BY p.id,

u.email;
END $$ LANGUAGE plpgsql;

It created successfully, but when call function SELECT create_post('haha', 1652131509, 'photo', 2); get error structure of query does not match function result type. Used tables:

CREATE TABLE "users" (
    "id" BIGSERIAL PRIMARY KEY,
    "email" text UNIQUE NOT NULL,
    "encrypted_password" text NOT NULL,
    "created_at" timestamp NOT NULL
);
CREATE TABLE "posts" (

"id" BIGSERIAL PRIMARY KEY,

"text" text,

"created_at" timestamp NOT NULL,

"photo_url" text,

"user_id" bigint NOT NULL,

FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE

);

I am migrating a system using Oracle DB to Postgres (12.7). Various checks are run weekly against the existing DB, including a RMAN.VALIDATE_DATABASE (actually RDSADMIN.RDSADMIN_RMAN_UTIL.VALIDATE_DATABASE as it's Amazon RDS) - physical+logical type. Is there an equivalent to this with Postgres?

The Oracle DB runs as an Amazon RDS instance, the Postgres DB is running as an Amazon Aurora instance.

I can see that I can enable block checksums, but I don't see anything else.

Thanks!