Patroni pkg_resources.DistributionNotFound
Hi, I installed PostgreSQL cluster using Patroni and HAProxy on CentOS7. OS: centos 7 DB:postgreSQL 12 patroni : patroni-1.6.5-1 But when the patroni started that error occured. Are there any suggestion ? Regards Dennis [root@Centos7-04 ~]# sudo systemctl status patroni -l ● patroni.service - Runners to orchestrate a high-availability PostgreSQL Loaded: loaded (/usr/lib/systemd/system/patroni.service; enabled; vendor preset: disabled) Active: failed (Result: exit-code) since Fri 2022-01-07 15:47:26 +03; 47s ago Process: 1699 ExecStart=/usr/bin/patroni /etc/patroni/patroni.yml (code=exited, status=1/FAILURE) Main PID: 1699 (code=exited, status=1/FAILURE) Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: File "/usr/lib/python3.6/site-packages/pkg_resources/__init__.py", line 570, in _build_master Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: ws.require(__requires__) Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: File "/usr/lib/python3.6/site-packages/pkg_resources/__init__.py", line 888, in require Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: needed = self.resolve(parse_requirements(requirements)) Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: File "/usr/lib/python3.6/site-packages/pkg_resources/__init__.py", line 774, in resolve Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: raise DistributionNotFound(req, requirers) Jan 07 15:47:26 Centos7-04.localdomain patroni[1699]: pkg_resources.DistributionNotFound: The 'urllib3!=1.21,>=1.19.1' distribution was not found and is required by patroni Jan 07 15:47:26 Centos7-04.localdomain systemd[1]: patroni.service: main process exited, code=exited, status=1/FAILURE Jan 07 15:47:26 Centos7-04.localdomain systemd[1]: Unit patroni.service entered failed state. Jan 07 15:47:26 Centos7-04.localdomain systemd[1]: patroni.service failed. [root@Centos7-04 ~]#
java.lang.ClassNotFoundException: batchprint.Batch
Hi, I have a function with below definition: CREATE OR REPLACE FUNCTION onesam.batchprint_individual( p1 character varying, p2 character varying, p3 character varying, p4 boolean, p5 boolean, p6 character varying, p7 integer, p8 integer) RETURNS integer LANGUAGE 'javau' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ batchprint.Batch.individual(java.lang.String, java.lang.String, java.lang.String, java.lang.Boolean, java.lang.Boolean, java.lang.String, java.lang.Integer, java.lang.Integer) $BODY$; And I am trying to execute below query: SELECT batchprint_INDIVIDUAL( '96674538', '', '', 't', 'f', 'UsysOfficial', '1103003', '1020') and the ERROR I am getting is: ERROR: java.lang.ClassNotFoundException: batchprint.Batch SQL state: XX000 My postgres version is 11.14. Could anyone suggest any kind of solution for it, as I couldn't find anything informative from a database perspective. Regards, Atul
Re: java.lang.ClassNotFoundException: batchprint.Batch
> '1103003', > '1020') > > and the ERROR I am getting is: > ERROR: java.lang.ClassNotFoundException: batchprint.Batch > SQL state: XX000 > > My postgres version is 11.14. > > > Could anyone suggest any kind of solution for it, as I couldn't find anything > informative from a database perspective. > > And your CLASSPATH is what exactly? \! echo $CLASSPATH might show it > > Regards, > Atul
Timestamp with precision output format with trailing zeros
Hi, > > # select '2022-01-06 18:00:00.000+03'::timestamptz(3); > timestamptz > > 2022-01-06 18:00:00+03 Would it be more correct to output zero milliseconds if the column has precision defined? Regards, Eugene
Stream Replication not working
Hi All, I have implemented Stream replication in one of my environments, and for some reason even though all the health checks are showing that the replication is working, when I run manual tests to see if changes are being replicated, the changes are not replicated to the standby postgresql environment. I have been researching for two day and I cannot find any documentation that talks about the case I am running into. I will appreciate if anybody could take a look at the details I have detailed below and give me some guidance on where the problem might be that is preventing my changes for being replicated. Even though I was able to instantiate the standby while firewalld was enabled, I decided to disable it just in case that it was causing any issue to the manual changes, but disabling firewalld has not had any effect, I am still not able to get the manual changes test to be replicated to the standby site. As you will see in the details below, the streaming is working, both sites are in sync to the latest WAL but for some reasons the latest changes are not on the standby site. How is it possible that the standby site is completely in sync but yet does not contain the latest changes? Thanks in advance for any help you can give me with this problem. Regards, Allie Details: Master postgresql Environment postgresql=# select * from pg_stat_replication; -[ RECORD 1 ]+-- pid | 1979089 usesysid | 16404 usename | replacct application_name | walreceiver client_addr | client_hostname | client_port | 55096 backend_start| 2022-01-06 17:29:51.542784-07 backend_xmin | state| streaming sent_lsn | 0/35000788 write_lsn| 0/35000788 flush_lsn| 0/35000788 replay_lsn | 0/31000500 write_lag| 00:00:00.001611 flush_lag| 00:00:00.001693 replay_lag | 20:38:47.00904 sync_priority| 1 sync_state | sync reply_time | 2022-01-07 14:11:58.996277-07 postgresql=# postgresql=# select * from pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid ---+--++---+-+-++--+-+---+--+---+--- postgresql| t| t | t | t | t | t | -1 | | | t| |10 pg_monitor| f| t | f | f | f | f | -1 | | | f| | 3373 pg_read_all_settings | f| t | f | f | f | f | -1 | | | f| | 3374 pg_read_all_stats | f| t | f | f | f | f | -1 | | | f| | 3375 pg_stat_scan_tables | f| t | f | f | f | f | -1 | | | f| | 3377 pg_read_server_files | f| t | f | f | f | f | -1 | | | f| | 4569 pg_write_server_files | f| t | f | f | f | f | -1 | | | f| | 4570 pg_execute_server_program | f| t | f | f | f | f | -1 | | | f| | 4571 pg_signal_backend | f| t | f | f | f | f | -1 | | | f| | 4200 replacct | t| t | t | t | t | t | -1 | | | t| | 16404 (10 rows) postgresql=# postgresql=# create database test_replication_3; CREATE DATABASE postgresql=# postgresql=# select datname from pg_database; datname postgres postgresql template1 template0 stream test_replication test_replication_2 test_replication_3 (8 rows) postgresql=# postgresql=# SELECT pg_current_wal_lsn(); pg_current_wal_lsn 0/35000788 (1 row) postgresql=# Standby postgresql Environment postgresql=# select * from pg_stat_wal_receiver; -[ RECO
Re: md5 issues Postgres14 on OL7
Michael Paquier writes: > On Thu, Jan 06, 2022 at 11:40:04AM -0500, Tom Lane wrote: >> Also, I wonder if this shouldn't be unified with the SSLerrmessage() >> support found in be-secure-openssl.c and fe-secure-openssl.c. > Guess so. HEAD could be poked at for this part. I recall looking at > that once by that did not seem worth the complications. Yeah, I suppose there's only a couple lines of code to be saved, and the complexity of dealing with multiple memory allocation conventions would outweigh that. > What do you think? Hm, you still have cast-away-const in md5_crypt_verify and plain_crypt_verify. Can we adjust their APIs to make them return const char * as well (and then their API spec is that the caller must never free the string, rather than being vague about it)? The other thing that bothers me slightly is that it looks like some code paths could end up passing a NULL string pointer to ereport or sprintf, since you don't positively guarantee that an error will return a string there. I suppose this is safe since 3779ac62d, but I don't really want to start making API specs depend on it. regards, tom lane
Re: Tab-completion error...?
Fixed. Turned out to be LD_LIBRARY_PATH had a /USR/local/lib. Changed it to /usr/local/lib and everything's hunky-dory. On Tue, Jan 4, 2022 at 6:15 PM Tom Lane wrote: > "Theodore M Rolle, Jr." writes: > > ldd /usr/local/pgsql/bin/psql > > linux-vdso.so.1 (0xa2bef000) > > libpq.so.5 => /USR/local/lib/libpq.so.5 (0xa2aaf000) > > libreadline.so.8 => /usr/lib/libreadline.so.8 (0xa2a1d000) > > libpthread.so.0 => /usr/lib/libpthread.so.0 (0xa29ed000) > > libm.so.6 => /usr/lib/libm.so.6 (0xa2941000) > > libc.so.6 => /usr/lib/libc.so.6 (0xa27cd000) > > /lib/ld-linux-aarch64.so.1 => /usr/lib/ld-linux-aarch64.so.1 > > (0xa2bbe000) > > libncursesw.so.6 => /usr/lib/libncursesw.so.6 (0xa2748000 > > Hm, is /USR actually in caps, or did you change that for emphasis? > > > I'm at a loss as to where the /USR came from. > > It's not in config.log (compile time) > > nor in the psql executable (run time). > > I think it came out of /etc/ld.so.conf. > > BTW, by default PG would link psql using an rpath switch pointing at > /usr/local/pgsql/lib, which I assume is where your manual build > put its libpq.so. That's evidently not having success getting that > libpq.so to be used. Did you tell configure to --disable-rpath? > Or maybe move the installation after building it? > > regards, tom lane > -- GnuPG/PGP key: 0xDD4276BA +-+ | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 | | 58209 74944[59230 78164]06286 20899 86280 +--| | 34825 34211 70679*82148 08651 32823 06647 |May the spirit | | 09384 46095 50582 23172 53594 08128 48111 | of π spread | | 74502 84102 70193 85211 05559 64462 29489 |around the world. | | 54930 38196 44288 10975 66593 34461 28475 | PI VOBISCUM! | | 38196 44288 10975 66593 34461 28475 64823 +-| | 37867 83165 27120 19091 45648 56692 34603 48610 45432 6648... | ++
Re: md5 issues Postgres14 on OL7
On Fri, Jan 07, 2022 at 05:40:09PM -0500, Tom Lane wrote:
> Hm, you still have cast-away-const in md5_crypt_verify and
> plain_crypt_verify. Can we adjust their APIs to make them
> return const char * as well (and then their API spec is that
> the caller must never free the string, rather than being
> vague about it)?
Okay. Hmm. This requires a couple of extra const markers in the area
of authentication and SASL for the backend, but not much actually.
I thought first that it would be more invasive.
> The other thing that bothers me slightly is that it looks like
> some code paths could end up passing a NULL string pointer to
> ereport or sprintf, since you don't positively guarantee that
> an error will return a string there. I suppose this is safe
> since 3779ac62d, but I don't really want to start making API
> specs depend on it.
Sounds fair to me in the long term, even for non-assert builds. I
have added a small-ish wrapper routine in crytohash_openssl.c for this
purpose, with a name copied from {be,fe}-secure-openssl.c to ease any
future refactoring lookups if that proves to be worth in the future.
--
Michael
From 0e7ceca421ab309ce64d8f6fea7a714da08df56c Mon Sep 17 00:00:00 2001
From: Michael Paquier
Date: Sat, 8 Jan 2022 14:56:39 +0900
Subject: [PATCH v3] Improve error reporting for cryptohashes
---
src/include/common/cryptohash.h | 1 +
src/include/common/md5.h | 9 ++-
src/include/libpq/crypt.h | 7 +-
src/include/libpq/sasl.h | 4 +-
src/backend/commands/user.c | 4 +-
src/backend/libpq/auth-sasl.c | 2 +-
src/backend/libpq/auth-scram.c| 5 +-
src/backend/libpq/auth.c | 33
src/backend/libpq/crypt.c | 46 ++-
src/backend/replication/backup_manifest.c | 9 ++-
src/backend/utils/adt/cryptohashfuncs.c | 25 +++---
src/common/cryptohash.c | 57 +-
src/common/cryptohash_openssl.c | 93 +++
src/common/md5_common.c | 20 +++--
src/interfaces/libpq/fe-auth.c| 22 +-
contrib/uuid-ossp/uuid-ossp.c | 18 +++--
16 files changed, 282 insertions(+), 73 deletions(-)
diff --git a/src/include/common/cryptohash.h b/src/include/common/cryptohash.h
index ea1300d5d4..8e339c83ad 100644
--- a/src/include/common/cryptohash.h
+++ b/src/include/common/cryptohash.h
@@ -34,5 +34,6 @@ extern int pg_cryptohash_init(pg_cryptohash_ctx *ctx);
extern int pg_cryptohash_update(pg_cryptohash_ctx *ctx, const uint8 *data, size_t len);
extern int pg_cryptohash_final(pg_cryptohash_ctx *ctx, uint8 *dest, size_t len);
extern void pg_cryptohash_free(pg_cryptohash_ctx *ctx);
+extern const char *pg_cryptohash_error(pg_cryptohash_ctx *ctx);
#endif /* PG_CRYPTOHASH_H */
diff --git a/src/include/common/md5.h b/src/include/common/md5.h
index bbd2ec0165..942ca4242c 100644
--- a/src/include/common/md5.h
+++ b/src/include/common/md5.h
@@ -26,9 +26,12 @@
#define MD5_PASSWD_LEN 35
/* Utilities common to all the MD5 implementations, as of md5_common.c */
-extern bool pg_md5_hash(const void *buff, size_t len, char *hexsum);
-extern bool pg_md5_binary(const void *buff, size_t len, void *outbuf);
+extern bool pg_md5_hash(const void *buff, size_t len, char *hexsum,
+ const char **errstr);
+extern bool pg_md5_binary(const void *buff, size_t len, void *outbuf,
+ const char **errstr);
extern bool pg_md5_encrypt(const char *passwd, const char *salt,
- size_t salt_len, char *buf);
+ size_t salt_len, char *buf,
+ const char **errstr);
#endif /* PG_MD5_H */
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index ee60772e94..3238cf66d3 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -35,12 +35,13 @@ extern PasswordType get_password_type(const char *shadow_pass);
extern char *encrypt_password(PasswordType target_type, const char *role,
const char *password);
-extern char *get_role_password(const char *role, char **logdetail);
+extern char *get_role_password(const char *role, const char **logdetail);
extern int md5_crypt_verify(const char *role, const char *shadow_pass,
const char *client_pass, const char *md5_salt,
- int md5_salt_len, char **logdetail);
+ int md5_salt_len, const char **logdetail);
extern int plain_crypt_verify(const char *role, const char *shadow_pass,
- const char *client_pass, char **logdetail);
+ const char *client_pass,
+ const char **logdetail);
#endif
diff --git a/src/include/libpq/sasl.h b/src/include/libpq/sasl.h
index 7ba3f5f5bc..71cc0dc251 100644
--- a/src/include/libpq/sasl.h
+++ b/src/include/libpq/sasl.h
@@ -126,11 +126,11 @@ typedef struct pg_be_sasl_mech
int (*exchange) (void *state,
const char *input, int inputlen,
char **output, int *outputlen,
- char **logdetai
Re: Timestamp with precision output format with trailing zeros
Hi Eugene, > > # select '2022-01-06 18:00:00.000+03'::timestamptz(3); > > timestamptz > > > > 2022-01-06 18:00:00+03 > Would it be more correct to output zero milliseconds if the column has > precision defined? Only significant fractional are printed, e.g. with milliseconds > 0: # select '2022-01-06 18:00:00.001+03'::timestamptz(3); timestamptz 2022-01-06 16:00:00.001+01 I don't know if omitting trailing zeros is intended but it makes sense to me. Erik
create database hangs forever on WSL - autovacuum deadlock?
Hi All, this is a problem from slack channel; it looks like a deadlock. Create database hangs infinitely showing a lock in pg_stat_activity and being mutually locked by autovacuum workers. executing pg_reload_conf couple times releasing the lock. Why sighup helps in this situation? is it a problem with windows memory management? this is postgres running on windows wsl: PostgreSQL 14.1 (Ubuntu 14.1-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 2022-01-08 07:24:06.892 CET [2347] LOG: process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 1000.918 ms 2022-01-08 07:24:06.892 CET [2347] DETAIL: Processes holding the lock: 2308, 2318. Wait queue: 2117, 2347. pid | 2117 wait_event_type | Lock wait_event | object state| active backend_xid | backend_xmin | 749 query_id | query| create database mytest1; backend_type | client backend postgres 2347 0.0 0.0 221280 2388 ?Ss 07:24 0:00 postgres: 14/main: autovacuum worker waiting postgres 2308 0.0 0.0 221284 2420 ?Ss 07:15 0:00 postgres: 14/main: autovacuum worker waiting postgres 2318 0.0 0.0 221284 2420 ?Ss 07:16 0:00 postgres: 14/main: autovacuum worker waiting postgres 2347 0.0 0.0 221280 2388 ?Ss 07:24 0:00 postgres: 14/main: autovacuum worker waiting 2022-01-08 07:36:44.359 CET [2409] postgres@postgres LOG: duration: 0.183 ms statement: SELECT pg_reload_conf(); 2022-01-08 07:36:44.359 CET [1561] LOG: received SIGHUP, reloading configuration files 2022-01-08 07:36:44.361 CET [2117] postgres@postgres LOG: process 2117 acquired ShareLock on object 1 of class 1262 of database 0 after 759697.185 ms 2022-01-08 07:36:44.361 CET [2117] postgres@postgres STATEMENT: create database mytest1; 2022-01-08 07:36:44.361 CET [2347] LOG: process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 758470.143 ms 2022-01-08 07:36:44.361 CET [2347] DETAIL: Process holding the lock: 2117. Wait queue: 2347. 2022-01-08 07:36:46.191 CET [2409] postgres@postgres LOG: duration: 0.146 ms statement: SELECT pg_reload_conf(); 2022-01-08 07:36:46.191 CET [1561] LOG: received SIGHUP, reloading configuration files 2022-01-08 07:36:46.193 CET [2347] LOG: process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 760301.845 ms 2022-01-08 07:36:46.193 CET [2347] DETAIL: Process holding the lock: 2117. Wait queue: 2347. 2022-01-08 07:36:47.532 CET [2409] postgres@postgres LOG: duration: 0.113 ms statement: SELECT pg_reload_conf(); 2022-01-08 07:36:47.533 CET [1561] LOG: received SIGHUP, reloading configuration files 2022-01-08 07:36:47.534 CET [2347] LOG: process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 761643.434 ms 2022-01-08 07:36:47.534 CET [2347] DETAIL: Process holding the lock: 2117. Wait queue: 2347. 2022-01-08 07:36:52.496 CET [2409] postgres@postgres LOG: duration: 0.141 ms statement: SELECT pg_reload_conf(); 2022-01-08 07:36:52.496 CET [1561] LOG: received SIGHUP, reloading configuration files 2022-01-08 07:36:52.499 CET [2347] LOG: process 2347 still waiting for RowExclusiveLock on object 1 of class 1262 of database 0 after 766607.664 ms 2022-01-08 07:36:52.499 CET [2347] DETAIL: Process holding the lock: 2117. Wait queue: 2347. 2022-01-08 07:36:52.542 CET [2117] postgres@postgres LOG: duration: 767877.903 ms statement: create database mytest1; pozdrawiam, best regards, mit freundlichen Grüßen, Alicja Kucharczyk
