Re: Keeping state in a foreign data wrapper

[Ian Lawrence Barwick]

2020年8月4日(火) 1:24 Stelios Sfakianakis :
>
> Hi,
>
> I am trying to  implement a FDW in Postgres for accessing a web api and I 
> would like to keep information like for example the total number of requests 
> submiited. Ideally these data should be kept “per-user” and of course with 
> the proper locking to eliminate race conditions. So the question I have is 
> how to design such a global (but per user and database) state, using the C 
> FDW API of Postgres. I have gone through the API and for example I see 
> various fdw_private fields in the query planning structures and callbacks but 
> these do not seem to be relevant to my requirements. Another possiblity seems 
> to be to use shared memory 
> (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it is 
> even less clear how to do it.

Shared memory would probably work; you'd need to load the FDW via
"shared_preload_libraries" and have the FDW handler function(s) update
shared memory with whatever statistics you want to track. You could
then define SQL functions to retrieve the stored values, and possibly
persist them over server restarts by storing/retrieving them from a
file.

Look at "pg_stat_statements" for a good example of how to do that kind of thing.

Regards

Ian Barwick

-- 
Ian Barwick   https://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Keeping state in a foreign data wrapper

[Ian Lawrence Barwick]

2020年8月4日(火) 14:54 Stelios Sfakianakis :
> On 4 Aug 2020, at 06:25, Ian Lawrence Barwick  wrote:
>
> 2020年8月4日(火) 1:24 Stelios Sfakianakis :
>
> Hi,
>
>>> I am trying to  implement a FDW in Postgres for accessing a web api and I 
>>> would like to keep information like for example the total number of 
>>> requests submiited. Ideally these data should be kept “per-user” and of 
>>> course with the proper locking to eliminate race conditions. So the 
>>> question I have is how to design such a global (but per user and database) 
>>> state, using the C FDW API of Postgres. I have gone through the API and for 
>>> example I see various fdw_private fields in the query planning structures 
>>> and callbacks but these do not seem to be relevant to my requirements. 
>>> Another possiblity seems to be to use shared memory 
>>> (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it is 
>>> even less clear how to do it.
>
>
>> Shared memory would probably work; you'd need to load the FDW via
>> "shared_preload_libraries" and have the FDW handler function(s) update
>> shared memory with whatever statistics you want to track. You could
>> then define SQL functions to retrieve the stored values, and possibly
>> persist them over server restarts by storing/retrieving them from a
>> file.
>
>> Look at "pg_stat_statements" for a good example of how to do that kind of 
>> thing.
>
> Thank you Ian for the prompt reply! I will certainly have a look at 
> pg_stat_statements
>
> I also came across mysql_fdw (https://github.com/EnterpriseDB/mysql_fdw) that 
> features a connection pool shared across queries. It uses a hash table with 
> the serverid and userid as lookup key : 
> https://github.com/EnterpriseDB/mysql_fdw/blob/REL-2_5_4/connection.c#L55

This is essentially the same as what "postgres_fdw" and similar FDW
implementations do.

> The hash table is allocated in the cache memory context but it worries me 
> that 1) no locks are used, 2) the "ConnectionHash" variable is declared 
> static so in the multi-process architecture of Postgres could have been the 
> case that multiple copies of this exist when the shared library of mysql_fdw 
> is loaded?

The hash table is specific to each running backend so will only be
accessed by that process.

Pre-loading a shared library just gives the library an opportunity to
set up shared memory etc. You can always try adding one of the FDW
libraries to "shared_preload_libraries" and see what happens
(theoretically nothing).

Regards

Ian Barwick



>
> Best regards
> Stelios
>
>
> Regards
>
> Ian Barwick
>
> --
> Ian Barwick   https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: How to distribute products to shop by amount of sales

[Ian Lawrence Barwick]

2020年8月16日(日) 15:32 Andrus :

(...)
> PostgreSQL 9.3.5 is used.

Unable to help with the query, but assuming that's not a typo can I be
among the first to urge you to at the very least update to 9.3.25,
which contains almost half-a-decade's worth of fixes, many critical?
See: https://why-upgrade.depesz.com/show?from=9.3.5&to=9.3.25

Regards

Ian Barwick

Re: remgr installation and configuration steps required

[Ian Lawrence Barwick]

2020年10月17日(土) 23:25 Atul Kumar :
>
> Hi,
>
> I have 3 different servers, i want to configure streaming replication
> (1 master and 2 slaves) using repmanager.
>
> Please share the installation and configuration steps for the same.
>
> Note: the steps should be organized.

I suggest starting with the Quickstart Guide:

https://repmgr.org/docs/current/quickstart.html

This recent blog article might also be of use:


https://www.2ndquadrant.com/en/blog/how-to-automate-postgresql-12-replication-and-failover-with-repmgr-part-1/

(despite the title it is relevant for other PostgreSQL versions too).

Regards

Ian Barwick
-- 
EnterpriseDB: https://www.enterprisedb.com

Re: database shutting down

[Ian Lawrence Barwick]

2020年10月19日(月) 16:22 Atul Kumar :
>
> Hi,
>
> I am configuring repmgr, so in postgresql.conf when i changed the
> parameter share_preload_libraries='repmgr', my database server is not
> starting.
>
> When i comment that parameter, database server started.
>
>
> Please help why that parameter is stopping database to start.

Check the PostgreSQL log file. Most likely you'll see a message like
'could not access file "repmgr": No such file or directory", which indicates
repmgr is not installed correctly.

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: using psql 11.4 with a server 13.0 && meta commands

[Ian Lawrence Barwick]

2020年10月21日(水) 20:54 Matthias Apitz :
>
>
> Hello,
>
> I've found the following problem using psql 11.4 against a server 13.0:
>
> $ export LANG=en_US.UTF-8
> $ psql -Usisis -dsisis
> psql (11.4, server 13.0)
> WARNING: psql major version 11, server major version 13.
>  Some psql features might not work.
> Type "help" for help.
>
> sisis=# \d dbctest
> FEHLER:  Spalte c.relhasoids existiert nicht
> LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...
>  ^
> sisis=#
>
> Is this expected? Are there other bigger issues with commands. I see the
> message "Some psql features might not work.", but that this affects also
> "normal" commands of daily use...

Yes, this is fully expected - the system catalogue may change between
server versions, and
hence the SQL needed for the meta-commands. Any psql version should be
backwards-
compatible with older server versions, but older psql versions cannot
of course know what
SQL might needed for newer server versions. Hence the warning.


Regards

Ian Barwick
-- 
EnterpriseDB: https://www.enterprisedb.com

Re: How to get debuginfo from building source code

[Ian Lawrence Barwick]

2020年10月22日(木) 11:51 Hou, Zhijie :
>
> Hi
>
> My PostgreSQL 10.3 in Centos7.4 exited abnormally and generated core.dump.
>
> I want to analyze the core.dump but I does not find 
> postgresql10-debuginfo-10.3 in
> https://download.postgresql.org/pub/repos/yum/debug/10/redhat/rhel-7.4-x86_64/
>
> Can I get the debuginfo from building the source code ?
> If so, What compile options should I use ?

See instructions here:

  https://wiki.postgresql.org/wiki/Developer_FAQ#Compile-time


Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: Postgres read jsonb content from stdin

[Ian Lawrence Barwick]

2020年12月26日(土) 20:19 Markur Sens :
>
> Hello,
>
> I'm trying to build a few data pipelines with Unix tools but can't figure out 
> how to insert in a slurp mode (e.g. not COPY line by line) content inside a 
> variable.
>
> Consider the following script (using a heredoc)
>
> json_url="https://file.json";
> local_file="/tmp/a.json"
>
> curl -s -m 10 -A 'Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 
> Firefox/30.0' \
> --max-redirs 0 -o ${local_file} ${json_url}
>
> psql "$PG_URI" -qAt < create table if not exists (data jsonb);
>
> insert into my_table(data) values (pg_read_file('${local_file}')::jsonb)
> on conflict do nothing;
> SQL
>
> The question is, how can I achieve the same result, without having to hit the 
> disk due. to the temporary file.
> I tried running by using pg_read_file('/dev/stdin')::jsonb

It can be done like this:

$ curl http://localhost/json.txt
{"bar": "baz", "balance": 7.77, "active": false}

$ psql -v jsonval="`curl -s http://localhost/json.txt`"; -d
'host=localhost dbname=postgres user=postgres' 

Re: upgrade using logical replication

[Ian Lawrence Barwick]

2021年1月21日(木) 9:19 Mohamed Wael Khobalatte :

>
>
> On Wed, Jan 20, 2021 at 2:37 PM Michael Lewis  wrote:
>
>> Using pg_upgrade takes minutes for an in place upgrade. If you can allow
>> 1+ hour of downtime, it seems overly complicated to use logical replication.
>>
>
> I suppose the Atul's issue is what to do with the replicas. Once he does
> pg_upgrade, then he will need to provision new ones, no? I suppose in this
> case logical would be better, with the new instance itself having replicas.
> I haven't done it, and it's gonna require some setup time, definitely much
> longer than pg_upgrade then make do with one server until your new physical
> replicas are set up.
>

The replicas will need to be set up at some point anyway; with logical
replication the new cluster is ready to go once the new primary is fully
"seeded" (and the new replicas have caught up with that). Switchover can
then
take place whenever convenient, with minimal downtime, more time for
testing,
and the possibility of switching back if issues are encountered.

Potential downsides to this approach are that the database schema may need
to be
modified to be suitable for logical replication, and additional resources
may be
needed to host the old and new clusters simultaneously during the migration
process.

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: Issues with using plpgsql debugger using PG13 on Centos 7

[Ian Lawrence Barwick]

2021年2月2日(火) 12:06 Jain, Ankit :
(...)

> But got the following error –
>
> ERROR: could not load library "/usr/pgsql-13/lib/plugin_debugger.so":
> /usr/pgsql-13/lib/plugin_debugger.so: undefined symbol: LWLockAssign SQL
> state: 58P01
>
>
>
> Can you please help with getting the debugger working ?
>

It looks like the extension code was compiled against an older PostgreSQL
version (presumably 9.5 or earlier; LWLockAssign was removed in 9.6).

You'll need to make sure it's compiled against the major version you're
using.

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: Unable To Drop Tablespace

[Ian Lawrence Barwick]

2021年2月5日(金) 3:52 Pavan Pusuluri :

> Hi there
>
> We are trying to drop a table space on RDS Postgres . We have removed the
> objects etc, but it still won't drop.
>
> I have checked and there's no reference anywhere to this tablespace but it
> complains it's not empty.
>
> I checked if it is a default for a database, revoked all privileges on the
> tablespace.
>
> We dropped the database but underpinning tablespace remained but when I
> query to see if any reference i get no hits.
>
> "Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON
> c.reltablespace=t.oid where t.spcname='mytablespace'
>
> I dont find any objects referencing. Kindly let me know if anything else
> needs to be checked?
>

There's a handy function "pg_tablespace_databases()" to check which
databases
might still have objects in a database. There are a couple of useful
queries demonstrating
usage here:

  https://pgpedia.info/p/pg_tablespace_databases.html

Regards

Ian Barwick


-- 
EnterpriseDB: https://www.enterprisedb.com

Re: Should pgAdmin 3 be saved?

[Ian Lawrence Barwick]

2021年2月7日(日) 4:07 Gabriel Martin :

> Hello,
>
> I know that since the launch of pgAdmin 4 in 2016, pgAdmin has been using
> a web-based model, as announced as early as 2014. You only need to do a
> little research on the Web to understand all the reasons behind this
> choice, which I do not seek to question.
>
> However, I wonder if it would be relevant to keep pgAdmin 3 alive. I'm not
> so much talking about improving the interface of pgAdmin 3, but simply
> about keeping the software functional in new systems. I tried to install it
> in the latest versions of Linux Mint and Ubuntu, but the software keeps
> bugging, which it didn't do in older versions of the same systems 5 or 6
> years ago.
>
> Do you think there's a possibility that people could fix pgAdmin 3, even
> if it means making a fork or whatever? Because, although pgAdmin 4 and
> other tools such as OmniDB and DBeaver are probably excellent, some people,
> for various reasons, might want to continue using pgAdmin 3 in 2021. I'm
> not an expert in the field, but I'm thinking that maybe someone here might
> be able to save this nice little application software from disappearing by
> investing a few days to maintain it.
>

FWIW there is a fork here which is making some attempt to keep it up-to-date
with recent PostgreSQL versions:

https://github.com/AbdulYadi/pgadmin3

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: pg_stat_progress_vacuum empty when running vacuum full

[Ian Lawrence Barwick]

2021年2月12日(金) 20:01 Luca Ferrari :

> Hi all,
> I'm running 11.5 and I'm monitoring pg_stat_progress_vacuum every 2
> seconds, while doing from another connection per-table VACUUMs. Every
> vacuum last 30 or more seconds, so I was expecting to see a record
> within pg_stat_progress_vacuum, but nothing appears if the vacuum is
> full. I suspect this is due to vacuum full performing a side-by-side
> table rewriting, rather than in-place actions against the original
> table, but I'm not sure if this is real reason or if I'm missing
> something.
>
>
If you're executing VACUUM FULL, you should be looking at
pg_stat_progress_cluster:


https://www.postgresql.org/docs/current/progress-reporting.html#CLUSTER-PROGRESS-REPORTING

Though that doesn't appear until Pg 12.

Regards

Ian Barwick
-- 
EnterpriseDB: https://www.enterprisedb.com

Re: when is pg_stat_archiver.stats_reset modified?

[Ian Lawrence Barwick]

2021年2月18日(木) 20:21 Luca Ferrari :

> Hi all,
> running on 12.5, but I guess this does not mind, I cannot understand
> when the value of pg_stat_archiver.stats_reset field is modified.
> I've executed a pg_stat_reset() but it did not affected the specified
> field.
> I suppose that field is not "resettable" by the administrator, as I
> tried to, but then what drives changes?
>

As pg_stat_archiver shows cluster-wide stats, you need
pg_stat_reset_shared().

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: How to determine server's own IP address? inet_server_addr not working

[Ian Lawrence Barwick]

2021年2月22日(月) 8:42 David G. Johnston :

> On Sun, Feb 21, 2021 at 4:38 PM Guyren Howe  wrote:
>
>> The documentation says that inet_server_addr() does this, but on our
>> servers it is returning nothing.
>>
>
> "Returns the IP address on which the server accepted the current
> connection, or NULL if the current connection is via a Unix-domain socket."
>
> You will need to demonstrate that the connection you are checking from
> isn't being made via a Unix-domain socket.
>

Note also there's a bug in current versions where this function will
erroneously
return NULL if executed by a parallel worker, see [1]. This is fixed in
HEAD but
hasn't been backpatched [2].

[1]
https://www.postgresql.org/message-id/cad21aoat4ahp0uxq91qpd7nl009tnuyqe-b14r3mnsvojte...@mail.gmail.com
[2]
https://git.postgresql.org/pg/commitdiff/5a6f9bce8dabd371bdb4e3db5dda436f7f0a680f


Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: PostgreSQL Upgrade with Replication managed via repmgr

[Ian Lawrence Barwick]

2022年3月2日(水) 23:49 DAN LU :
>
> Hello,
>
> I am hoping someone can help me regarding upgrading PG to the latest release
> that has replication setup via repmgr.
>
> Here is an example of my current setup:
> Server A: PostgreSQL 12.1 serving as the "primary" role managed by repmgr 5.1 
> (https://repmgr.org/)
> Server B: PostgreSQL 12.1 serving as the "standby" role managed by repmgr 5.1 
> as well
>
> Goal:
> Upgrade both primary and standby PG to 14.1 with repmgr 5.1.
>
> I read the upgrade steps via
> https://www.postgresql.org/docs/14/pgupgrade.html.  Step #9 to step #11 is for
> instance with standby, but it does not say repmgr specifically.

repmgr is not part of the core PostgreSQL project, so like any non-core
extension or utility is not considered in the core documentation.

> I also find no literature online that others posted regarding repmgr in the
> mix of an upgrade.

The repmgr documentation discusses this situation:

  https://repmgr.org/docs/current/upgrading-and-pg-upgrade.html

Basically, you should upgrade repmgr to the latest version (5.3.1)
on the current (12.x) server. Otherwise the upgrade with pg_upgrade
will fail, as it will detect that the upgraded database is attempting
to reference extension functions which have been removed or renamed
(which is the case with this repmgr upgrade path)

BTW this issue is not specific to repmgr, and will be relevant for
any non-core extensions which use shared libraries.

> Upgrade both primary and standby PG to 14.1 with repmgr 5.1.

Actually, if that is your goal, you shouldn't need to take any
particular action for repmgr. However do bear in mind that repmgr 5.1
was released in April 2020, and there have been a number of improvements
and bugfixes since then.

I do suggest verifying the upgrade in a non-production environment first.


Regards

Ian Barwick



-- 
EnterpriseDB: https://www.enterprisedb.com

Re: pg_stat_activity.query empty

[Ian Lawrence Barwick]

2022年4月26日(火) 23:24 Laurenz Albe :
>
> On Tue, 2022-04-26 at 06:59 -0700, David G. Johnston wrote:
> > On Tue, Apr 26, 2022 at 6:55 AM Robert Lichtenberger 
> >  wrote:
> > > When executing "SELECT * FROM pg_stat_activity" I get lots of rows that
> > > have an empty "query" column.
> > >
> > > According to [1] this column should contain the "Text of this backend's
> > > most recent query", either a query that is currently running or the last
> > > query that was executed.
> > >
> > > So how comes that a lot of the rows in pg_stat_activity have an empty
> > > "query"?
> > >
> > > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
> >
> > They've never executed a query.  Probably due to connection pooling opening 
> > connections for the pool but never needing to hand them out.
>
> An alternative option is that the last query sent was an empty string.

Or it's the kind of backend which doesn't normally execute queries.

epp=# SELECT backend_type FROM pg_stat_activity WHERE query = '';
backend_type
--
autovacuum launcher
pglogical supervisor
logical replication launcher
pglogical manager 16389
background writer
checkpointer
walwriter
(7 rows)


Regards

Ian Barwick


-- 
EnterpriseDB: https://www.enterprisedb.com

Re: Question on cast string to date

[Ian Lawrence Barwick]

2022年5月10日(火) 13:28 正华吕 :
>
> Hi,
>
> I test the following SQL in pg15dev (seems same behavior as the previous 
> version).
>
> select '2020701'::date;
> date
>
> 0202-07-01
>(1 row)
>
>At the first glance, the result seems quite strange.
>
>Go through the code, postgres use date_in to do the cast, and firstly use 
> last 2 chars to
>get the day, and the 2 chars to get the month, and all remaining chars as 
> year.
>
>The question here is: should we throw error for such input? Or what 
> standard postgres is
>using to cast such kind of string?

This is ISO-8601 format, see here for a list of possible input formats:

  
https://www.postgresql.org/docs/current/datatype-datetime.html#id-1.5.7.13.18.5


Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: show primary_conninfo unchanged after promotion?

[Ian Lawrence Barwick]

2022年5月18日(水) 23:07 Wiwwo Staff :
>
> Hi!
> i might be doing something wrong, but I cannot see what...
>
> Server is 14.3 (Debian 14.3-1.pgdg110+1)
> (at the time of writing, in Dockerfile FROM postgres:14)
>
> I have a 3 node cluster, starting with pg_red (172.26.0.2) as primary.
> I promote pg_green (172.26.0.4) as new primary, all other aligned.
>
> Now, on pg_green (172.26.0.4):
>
> =# select inet_server_addr();
>  inet_server_addr
> --
>  172.26.0.4
>
> =# select pg_is_in_recovery();
>  pg_is_in_recovery
> ---
>  f
>
> =# select * from pg_stat_replication; (CUT)
>  pid | usesysid | usename | application_name | client_addr
> -+--+-+--+-
>  486 |   16,384 | rep | walreceiver  | 172.21.0.2
>  625 |   16,384 | rep | walreceiver  | 172.21.0.4
>
> But, if I execute show primary_conninfo, i get
> =# show primary_conninfo ;
>   
> primary_conninfo
> 
>  user=rep passfile='/.pgpass' channel_binding=prefer host=pg_red port=5432 
> sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 g.
> .ssencmode=prefer krbsrvname=postgres target_session_attrs=any
>
> which says primary is pg_red, which is the old primary.

You're not doing anything wrong, it's just that the value of
primary_conninfo is left untouched after promotion. It is however
ignored if the server is a primary; see:

  
https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-PRIMARY-CONNINFO

which states: "This setting has no effect if the server is not in standby mode."

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re:

[Ian Lawrence Barwick]

2022年6月8日(水) 19:51 Jim McNamara :
>
> Hi -
>
> Does anyone use the gawk extension? I think it is the coolest thing.
>
> I see that it installed .so files on my system from the fedora package 
> manager but there is no .sql file or control file in the extension directory.
>
> I searched with kfind utility and didn't find anything.
>
> I can't seem to use create extension to get it installed.
>
> Please help if you know the trick.

Are you talking about this: http://gawkextlib.sourceforge.net/pgsql/pgsql.html ?

I haven't used it, but from the description it sounds like an extension for the
gawk utility which provides PostgreSQL connectivity via libpq, not an extension
to be installed in PostgreSQL.

Regards

Ian Barwick

Re: message log merge (streaming replication)

[Ian Lawrence Barwick]

2022年6月9日(木) 14:32 Peter Adlersburg :
>
> Dear fellow DBA's,
>
>
> While troubleshooting one of our production replication clusters (phys. 
> streaming replication using the patroni framework)
> I stumbled over a - at least for me - strange phenomenon in the postgres logs 
> of the two cluster members:
>
> *** node-01 ***
>
> [postgres@db-node-01 main]$ grep 'LOG:  database' postgresql-2022-06-05.log
> time=2022-06-05 18:25:26 CEST, pid=1720 LOG:  database system is shut down
> time=2022-06-05 18:25:29 CEST, pid=3252374 LOG:  database system was shut 
> down at 2022-06-05 18:25:23 CEST
> time=2022-06-05 18:25:31 CEST, pid=3252371 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 18:29:11 CEST, pid=3252371 LOG:  database system is ready to 
> accept connections
> time=2022-06-05 18:32:01 CEST, pid=1816 LOG:  database system was interrupted 
> while in recovery at log time 2022-06-05 18:29:11 CEST
> time=2022-06-05 18:32:03 CEST, pid=1813 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 19:00:26 CEST, pid=1813 LOG:  database system is ready to 
> accept connections
>
> *** node-02 ***
>
> [postgres@db-node-02 main]$ grep 'LOG:  database' postgresql-2022-06-05.log
> time=2022-06-05 18:25:26 CEST, pid=1720 LOG:  database system is shut down
> time=2022-06-05 18:25:29 CEST, pid=3252374 LOG:  database system was shut 
> down at 2022-06-05 18:25:23 CEST
> time=2022-06-05 18:25:31 CEST, pid=3252371 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 18:29:11 CEST, pid=3252371 LOG:  database system is ready to 
> accept connections
> time=2022-06-05 18:32:01 CEST, pid=1816 LOG:  database system was interrupted 
> while in recovery at log time 2022-06-05 18:29:11 CEST
> time=2022-06-05 18:32:03 CEST, pid=1813 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 19:00:26 CEST, pid=1813 LOG:  database system is ready to 
> accept connections
>
> The output is by no means complete - I only kept the duplicate entries.
>
> My question:
>
> How is it possible that the error logs are 'merged' across the two database 
> nodes?

That's impossible to determine on the basis of the available information.

How is logging set up? What kind of environments are the nodes running in?
Is it possible they are able to write to a shared disk of some sort?

> Are the message/error-logs also replicated?

No.

> Is this the intended behaviour?

It's not typical behaviour, but I suppose it's conceivable someone designed
such a setup for some reason.

Regards

Ian Barwick

Re: [BeginnerQuestion]Why these is 6 rows in my SELECT statement?

[Ian Lawrence Barwick]

2022年9月8日(木) 17:37 BeginnerC :
>
> Hello community,
> I am checking my pg_stat_activity view,but something confused me.
> Just like this:
>
> postgres=# SELECT wait_event_type, wait_event FROM pg_stat_activity;
>  wait_event_type | wait_event
> -+-
>  Activity| AutoVacuumMain
>  Activity| LogicalLauncherMain
>  |
>  Activity| BgWriterHibernate
>  Activity| CheckpointerMain
>  Activity| WalWriterMain
> (6 rows)
>
> As you can see, the third row is empty,but I don't know why.
> Can anyone provide some solution to me?

It's empty because that's your own client session and it's not waiting
on anything :).

 postgres=# SELECT pg_backend_pid();
 pg_backend_pid

  24941
(1 row)

   postgres=# SELECT pid, backend_type, wait_event_type, wait_event
FROM pg_stat_activity;
  pid  | backend_type | wait_event_type |
wait_event

---+--+-+-
 20859 | autovacuum launcher  | Activity| AutoVacuumMain
 20860 | logical replication launcher | Activity|
LogicalLauncherMain
 21554 | walsender| Activity| WalSenderMain
 24941 | client backend   | |
 20856 | background writer| Activity| BgWriterHibernate
 20855 | checkpointer | Activity| CheckpointerMain
 20858 | walwriter| Activity| WalWriterMain
(7 rows)

Regards

Ian Barwick

Re: About foreign data wrapper

[Ian Lawrence Barwick]

2022年10月17日(月) 16:36 Rama Krishnan :
>
> Hi all,
>
> What Is the difference between dblink and foreign data wrapper?

Basically, dblink enables you to execute individual queries on a remote
PostgreSQL server via a function and use the results in a local query e.g.:

  SELECT *
  FROM dblink('myconn', 'SELECT aid, bid, abalance FROM
pgbench_accounts ORDER BY 1')

whereas a foreign data wrapper enables you to query relations on a remote
server (which can also be a data source other than PostgreSQL) directly
within the local query.

Regards

Ian Barwick

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

[Ian Lawrence Barwick]

2022年10月27日(木) 11:00 David G. Johnston :
>
> On Wed, Oct 26, 2022 at 6:33 PM Bryn Llewellyn  wrote:
>>
>> The descriptive designation "the role that owns the SQL part of the 
>> implementation of PostgreSQL" is too much of a mouthful for daily use.
>
>
> Don't think it's documented but I like "bootstrap user" which I've seen 
> bandied about here a bit.

"bootstrap superuser" is also mentioned a few times in the docs, see e.g.:

https://www.postgresql.org/docs/devel/sql-grant.html

This recent commit: e530be2c5ce77475d56ccf8f4e0c4872b666ad5f [1] might
also be of interest
to anyone considering the "special-ness" of this role.

[1] 
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e530be2c5ce77475d56ccf8f4e0c4872b666ad5f
Regards

Ian Barwick

Re: Setting up replication on Windows, v9.4

[Ian Lawrence Barwick]

2022年11月5日(土) 7:59 Brad White :
>
> I'm setting up a backup for our primary postgres server using the archived 
> WAL files.
> Then I'll try to upgrade it to Streaming Replication.
> Then I'll upgrade the system to v.latest.
> For now, we are on v.9.4.
>
> I do a base backup from the primary to a directory on the NAS.
>
>   "C:\Program Files\PostgreSQL\9.4\bin\pg_basebackup.exe" -D 
> \\diskstation\AccessData\Dev\Backup -P -X s -v -h 192.168.1.118 -p 5432 -U 
> postgres
>
> That appears to go fine.
> Then I delete data\*.* and copy everything except the config files from the 
> backup into data.
>
> Copy in recovery.conf
> --
> standby_mode  = 'on'
> primary_conninfo  = 'host=192.168.1.118 port=5432 user=replication 
> password=**'
> restore_command = 'copy "DISKSTATION\\AccessData\\WALfiles\\%f" "%p"'
> --
> Copy in postgresql.conf, with settings
> --
> listen_addresses = '127.0.0.1,192.168.1.118'
> wal_level = archive
> hot_standby = on
> --

Is this the postgresql.conf applied to the standby? Just wondering
as one of the listen_addresses is the same as the host in primary_conninfo.

> Interestingly, the recovery file says
> # Note that recovery.conf must be in $PGDATA directory.
> # It should NOT be located in the same directory as postgresql.conf
> Those seem contradictory.

I don't know where those lines come from, they're not generated by PostgreSQL.

It is certainly true that recovery.conf *must* be in the $PGDATA directory.  The
above lines would make sense if it's expected that postgresql.conf will be
located in another location (as is usually the case with Debian/Ubuntu
packages), but unless your setup is specifying that, just put both files in
$PGDATA.

> And if I remove the postgresql, it just refuses to start.
>
> With all this in place, I start the service, it runs for a bit, then shuts 
> down.
> No errors in the event log.
> 5 postgres processes are left running along with a pid file.

I'm not familiar with Windows, but it sounds like PostgreSQL is
actually running.
What happens if you try and connect to it?

> The log file says
> --
> LOG:  database system was interrupted while in recovery at log time 
> 2022-11-04 13:17:28 PDT
> HINT:  If this has occurred more than once some data might be corrupted and 
> you might need to choose an earlier recovery target.
> FATAL:  the database system is starting up
> FATAL:  the database system is starting up
> FATAL:  the database system is starting up
> LOG:  entering standby mode
> LOG:  consistent recovery state reached at 6A/3590
> LOG:  record with zero length at 6A/3590
> LOG:  started streaming WAL from primary at 6A/3500 on timeline 1
> LOG:  redo starts at 6A/3590
> FATAL:  the database system is starting up
> FATAL:  the database system is starting up
> FATAL:  the database system is starting up
> 
> FATAL:  the database system is starting up
> FATAL:  the database system is starting up
>
> So it seems that the backup didn't work as well as first appeared.

The above state looks very much what would happen if the standby is running with
"hot_standby = off", and connection attempts are being made. Try attempting
to connect to it.

Also, check if there's an entry in pg_stat_replication on the primary.


Regards

Ian Barwick

Re: Setting up replication on Windows, v9.4

[Ian Lawrence Barwick]

2022年11月5日(土) 10:02 Ian Lawrence Barwick :
>
> 2022年11月5日(土) 7:59 Brad White :
...
> > Interestingly, the recovery file says
> > # Note that recovery.conf must be in $PGDATA directory.
> > # It should NOT be located in the same directory as postgresql.conf
> > Those seem contradictory.
>
> I don't know where those lines come from, they're not generated by PostgreSQL.
>
> It is certainly true that recovery.conf *must* be in the $PGDATA directory.  
> The
> above lines would make sense if it's expected that postgresql.conf will be
> located in another location (as is usually the case with Debian/Ubuntu
> packages), but unless your setup is specifying that, just put both files in
> $PGDATA.
...

Hmm, looks like those lines came from here:

 https://wiki.postgresql.org/wiki/Streaming_Replication

That's plain wrong and confusing [1], I'll update.

[1] not for the first time either:
https://serverfault.com/questions/760802/why-should-postgresql-conf-not-be-in-the-same-dir-as-recovery-conf

Ian Barwick

Re: List user databases

[Ian Lawrence Barwick]

2022年11月10日(木) 13:41 Igor Korot :
>
> Hi, ALL,
> According to 
> https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
> there are generally 3 system DBs.
>
> However I'm confused with the word general.
> How many system databases can be made on a PG server?

template0 and template1 are the mandatory system databases which
cannot be dropped.

"postgres" is created by default for operational convenience, but is
not essential and can
be removed if you really want to.

Regards

Ian Barwick

Re: List user databases

[Ian Lawrence Barwick]

2022年11月10日(木) 14:16 Adrian Klaver :
>
> On 11/9/22 20:57, Ian Lawrence Barwick wrote:
> > 2022年11月10日(木) 13:41 Igor Korot :
> >>
> >> Hi, ALL,
> >> According to 
> >> https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/,
> >> there are generally 3 system DBs.
> >>
> >> However I'm confused with the word general.
> >> How many system databases can be made on a PG server?
> >
> > template0 and template1 are the mandatory system databases which
> > cannot be dropped.
>
> Actually that is not strictly true:
>
> https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
>
> "
> Note
>
> template1 and template0 do not have any special status beyond the fact
> that the name template1 is the default source database name for CREATE
> DATABASE. For example, one could drop template1 and recreate it from
> template0 without any ill effects. This course of action might be
> advisable if one has carelessly added a bunch of junk in template1. (To
> delete template1, it must have pg_database.datistemplate = false.)

OK, "cannot be dropped unless you manually convert them into non-template
databases" :).

Regards

Ian Barwick

Re: Database size different on Primary and Standby?

[Ian Lawrence Barwick]

2023年1月19日(木) 8:50 Erik Wienhold :
>
> > On 19/01/2023 00:09 CET Hilbert, Karin  wrote:
> >
> > I manage some PostgreSQL clusters on Linux. We have a Primary & two Standby
> > servers & for Production, there is also a DR server. We use repmgr for our 
> > HA
> > solution & the Standbys are cloned from the Primary using the repmgr standby
> > clone command.
> >
> > My manager asked for a report of all the user databases & their sizes for 
> > each
> > server in the cluster. I used the psql "\l+" command & then extracted the
> > database name & the size from the output. I expected the databases to be the
> > same size on the Standbys as on the Primary, but I found that some of the
> > databases were smaller on the Standby servers than on the Primary.
> >
> > For example, the output on the Primary for one of the user databases showed
> > as: 8997 kB, but on the Standbys, it was 8849 kB.
>
> The standbys could be missing some indexes because schema changes are not
> replicated and must be applied manually.

This is incorrect; with streaming replication all changes applied on the primary
are applied on the standby.

(...)
> Could also be different page sizes. But that's a compilation option. What does
> SHOW block_size say on those systems?

It is impossible to start a standby using binaries built with a
different block size to
the primary.

Regards

Ian Barwick

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

[Ian Lawrence Barwick]

2023年2月19日(日) 9:51 Christophe Pettus :
>
>
>
> > On Feb 18, 2023, at 15:49, Bryn Llewellyn  wrote:
> >
> > I’ve searched in vain for an account of how "autocommit" mode actually 
> > works.
>
> I realize now I may have misinterpreted your question... apologies if so!  If 
> you mean the BEGIN and COMMIT statement that some client libraries insert 
> into the stream when autocommit is off, that's the client, not PostgreSQL.
>
> PostgreSQL has no idea that mode even exists: it either sees statements 
> without transactions, which run in their own transaction, or BEGIN / COMMIT 
> statements.  Because client stacks have traditionally loved to provide their 
> own transaction semantics, they might inject BEGIN and COMMIT statements, but 
> that's not something PostgreSQL sees.

Historical trivia: PostgreSQL had a (backend) "autocommit" GUC in 7.3
only, which remained as
a dummy GUC until 9.5 (see: https://pgpedia.info/a/autocommit.html ).

Regards

Ian Barwick

Re: Quit currently running query

[Ian Lawrence Barwick]

2023年2月28日(火) 22:30 Albert Cornelius :
>
> How can I quit a currently running query? I've issued a query and my server 
> does not respond anymore. Is there another solution than using kill -9?

See here:

  
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

Regards

Ian Barwick

Re: Is there psql cluster or replication

[Ian Lawrence Barwick]

2023年3月22日(水) 8:47 Vince McMahon :
>
> Is there cluster on postgres?  Or, there is replication, not cluster.
>
> If there is active/passive cluster or active/active , may I have the steps to 
> configure them?

See here for details about PostgreSQL's built-in streaming replication.

https://www.postgresql.org/docs/current/high-availability.html

Regards

Ian Barwick

Re: Possible old and fixed bug in Postgres?

[Ian Lawrence Barwick]

2023年4月6日(木) 0:02 Steve Rogerson :
>
> On 05/04/2023 11:23, Erik Wienhold wrote:
> > Judging by the commit message and changed test cases, probably:
> >
> > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=921d749bd4c34c3349f1c254d5faa2f1cec03911
> >
> That's the one. I can't see which pg version(s) this turned up in.

$ git tag --contains 921d749bd4c34c3349f1c254d5faa2f1cec03911 | head -1
REL8_0_0

Regards

Ian Barwick

Re: VM Instance to Google Cloud SQL Migration

[Ian Lawrence Barwick]

2018年11月15日(木) 17:19 Andreas Kretschmer :
>
> Am 15.11.2018 um 08:54 schrieb Sathish Kumar:
> > We would like to migrate our Postgresql VM instance on Google Cloud
> > Platform to Google Cloud SQL with a minimal downtime. As I checked, we
> > have to export and import the SQL file and our database size is large
> > and cannot afford longer downtime.
> >
> > Do any have solution to achieve this?.
>
> setup a replication from one to the other?

Doesn't seem possible at the moment; here:
https://cloud.google.com/sql/docs/postgres/replication/
it says: "Cloud SQL for PostgreSQL does not yet support replication
from an external
master or external replicas for Cloud SQL instances."

Looking at the feature list:

  https://cloud.google.com/sql/docs/postgres/features

among the "Unsupported features" are: "Any features that require
SUPERUSER privileges"
(apart from a limited number of extensions), which pretty much rules
out pglogical or similar solutions.


Regards

Ian Barwick

--
 2ndQuadrant - The PostgreSQL Support Company.
 www.2ndQuadrant.com

Re: Wals not recycling

[Ian Lawrence Barwick]

2018年12月20日(木) 21:18 Rijo Roy :
>
> Hi All,
>
> I am facing an issue with wal recycling in one of my Postgresql servers 
> running ina rhel box and the PG version is 10. The issue is that wal files 
> are not recycling at all. Please note that its a qa server and the load is 
> quite high on it. It is running in no archive mode with fsync on, 
> max_wal_size is 8GB with checkpoint_timeout of 5mins and default 
> checkpoint_completion_target of 0.5. Could you please give me some pointers 
> what could be wrong, if it is hardware that is problem here, how can I 
> collect some facts about it so that 8 can take this matter with system guys.
> Average wal generation is approximately 9k per day
> I have enabled log_checkpoint in my server and I can see it says checkpoint 
> complete: wrote ###buffers, 0 wal files added, 0 removed, 0 recycled 
> LogCheckpointEnd
> I have no active replication slots in this database cluster.Also the 
> wal_keep_segments is set to 0. Appreciate your help!

You say you have no *active* replication slots - does that imply you
have at least one *inactive* replication slot? If so, that will cause
WAL files to be retained indefinitely.


Regards

Ian Barwick

-- 
   Ian Barwick   http://www.2ndQuadrant.com/
   PostgreSQL Development, 24x7 Support, Training & Services

Re: PostgreSQL version compatibility with RHEL7.6

[Ian Lawrence Barwick]

2018年12月20日(木) 19:56 Deepti Sharma S :

> Hello Team,
>
>
>
> Can you please let us know which version of PostgreSQL DB is compatible
> with RHEL7.6 and if not available when we can expect the same.
>

Community packages for all community-supported PostgreSQL versions (at the
time of writing: 9.4 ~ 11) are available via the PGDG yum repository for
RHEL7; see: https://yum.postgresql.org/repopackages.php

Regards

Ian Barwick

-- 
   Ian Barwick   http://www.2ndQuadrant.com/

   PostgreSQL Development, 24x7 Support, Training & Services

Re: Wals not recycling

[Ian Lawrence Barwick]

2018年12月20日(木) 21:58 Rijo Roy :
>
> Thanks sk for replying Ian.. I have one inactive replication slot in the 
> database. I suspected that could be the cause but was not sure of it. Could 
> you please tell me what I should be doing in this case. Can I drop the 
> replication slot since it is no more used and delete old wal files or will PG 
> be able to recycle them by itself.

Yes, just drop the replication slot and the excess files will be removed.

Regards

Ian Barwick

--
  Ian Barwick  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'

[Ian Lawrence Barwick]

2023年4月25日(火) 21:42 Matthias Apitz :
>
>
> Hello,
>
> We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
> On fetch empty char columns are returned as (Perl) 'undef'
>
>   while ( my @row_ary = $dba->FetchArray()) {
>foreach my $i (0..$#row_ary) {
> if ($row_ary[$i] eq undef)  {
> print $row_ary[1] . "\n";
> next;
> }
> ...
> which later leads in our code to NULL values '\N' in the writing of a 
> CSV-like export
> files. Ofc NULL values in the database are something else as '' char
> strings.
>
> How this must be distinguished with DBD::Pg?

"eq undef" looks very wrong there:

$ perl -e "printf(qq|%i\n|, '' eq undef);"
1
$ perl -e "printf(qq|%i\n|, defined '');"
1

You probably want "if (!defined $row_ary[$i])". And possibly warnings enabled:

$ perl -w -e "printf(qq|%i\n|, '' eq undef);"
Use of uninitialized value in string eq at -e line 1.
1
$ perl -w -e "printf(qq|%i\n|, defined '');"
1

Regards

Ian Barwick

Re: How to make the generate_series to generate the letter series?

[Ian Lawrence Barwick]

2023年5月29日(月) 12:39 Wen Yi <896634...@qq.com>:
>
> Hi team,
> when I study the generate_series function, I found that it can not only 
> generate the number series but also can generate the date series.(ref: 
> https://www.postgresql.org/docs/current/functions-srf.html)
> That means I can make the generate_series to generate the letter series.
>
> So I try this command (I guess I can follow the ascil code rule to complete 
> this work):
>
> # SELECT * FROM generate_series('a'::int, 'z'::int, 1);
>
> But the postgres reply me a error, because the 'a' can't translate into 
> 'integer' type.
>
> Can someone provide me a better solution?
> Thanks in advance!

SELECT chr(x + 96) FROM generate_series(1, 26) x

Regards

Ian Barwick

Re: initdb can't init the database

[Ian Lawrence Barwick]

2023年6月10日(土) 14:30 Wen Yi <896634...@qq.com>:
>
> Hi team,
> I use the source to install the postgresql 15.3 (Fedora Linux 37), I create a 
> install.sh as this:
>
> install.sh:
>
> sudo rm -r /home/postgres/pgsql
> sudo mkdir /home/postgres/pgsql
> sudo rm -r /home/postgres/pgsql/lib
> sudo mkdir /home/postgres/pgsql/lib
> ./configure --prefix=/home/postgres/pgsql/bin 
> --exec-prefix=/home/postgres/pgsql/lib --enable-debug --enable-coverage 
> --enable-dtrace --enable-cassert --enable-profiling
> sudo make install
> sudo useradd -m postgres
> sudo passwd -d postgres
> make clean
>
> Then, I start to execute this shell script:
>
>
> [beginnerc@bogon postgresql-15.3]$ ./install.sh
> [sudo] beginnerc's passwod：
> ...
> [beginnerc@bogon postgresql-15.3]$ su postgres
> bash-5.1$ ./initdb
> initdb: error: no data directory specified
> initdb: hint: You must identify the directory where the data for this 
> database system will reside.  Do this with either the invocation option -D or 
> the environment variable PGDATA.
(...)
> Can someone give me some advice?
> Thanks in advance!

The clue is right there after you execute initdb.

You also might want to check the ownership of those directories being created
by the script.

Regards

Ian Barwick

Re: Is anyone using db_user_namespace?

[Ian Lawrence Barwick]

2023年7月11日(火) 2:45 Nathan Bossart :
>
> On Fri, Jun 30, 2023 at 02:56:08PM -0700, Nathan Bossart wrote:
> > Over in pgsql-hackers, I've proposed removing the db_user_namespace
> > parameter in v17 [0].  I am personally not aware of anyone using this
> > parameter, but I wanted to give folks an opportunity to object in case they
> > are using it (or are aware of someone who is).
> >
> > [0] https://postgr.es/m/20230630200509.GA2830328%40nathanxps13
>
> Bumping this thread.  As long as no objections materialize, I plan to
> remove this parameter shortly.

Never used it myself; funnily enough I stumbled on it a few weeks back
and wondered if it was some nifty feature I'd overlooked, then I realized
I've stumbled across it before several times and each time promptly
forgotten about it.

Moreover a quick Google search (in quotation marks) brings up a
few pages mainly of documentation copies, configuration file snippets
happening to contain "db_user_namespace", a few mostly very non-recent
mailing list/forum questions, but nothing which screams "this feature
is used widely".

Regards

Ian Barwick

Re: [EXTERNAL] Oracle FDW version

[Ian Lawrence Barwick]

2023年8月23日(水) 12:47 Christophe Pettus :
>
>
>
> > On Aug 22, 2023, at 19:57, Jethro Elmer Sanidad 
> >  wrote:
> >
> > Hello,
> >
> > I tried both the 1.5.0 and 2.0.0. Both returned error during 'make' 
> > command. Please see below:
>
> The API between PostgreSQL and foreign data wrappers has changed 
> significantly since 9.4.  As Tom mentioned, you need to upgrade your server 
> to something in-support.

FWIW oracle_fdw should actually build against PostgreSQL as far back as 9.3
(though finding packages would be a fruitless endeavour); the documentation
does explicitly mention that it will likely not build against forks which are
not compatible with the community code [1].

[1] https://github.com/laurenz/oracle_fdw#5-installation-requirements

Regards

Ian Barwick

Re: [EXTERNAL] Oracle FDW version

[Ian Lawrence Barwick]

2023年8月23日(水) 15:10 Jethro Elmer Sanidad :

> Hello,
>
> Can you confirm in this email that our current version of PostgreSQL
> (9.2.24) is not compatible with any of oracle_fdw versions released? And
> you are recommending an upgrade? Thanks!
>

OK, here's the summary:

- oracle_fdw builds against *community* PostgreSQL 9.3 and later (I just
verfied that)
- it is likely not compatible with PostgreSQL forks such as Greenplum
- if you are running a version of PostgreSQL based on 9.2.x or 9.4.x it's
hopelessly
  out-of-date and you should definitely upgrade anyway (but if it's a fork,
that still won't
  that mean oracle_fdw will compile against it)

HTH

Ian Barwick



>
> On Wed, Aug 23, 2023 at 2:04 PM Ian Lawrence Barwick 
> wrote:
>
>> 2023年8月23日(水) 12:47 Christophe Pettus :
>> >
>> >
>> >
>> > > On Aug 22, 2023, at 19:57, Jethro Elmer Sanidad <
>> jethroelmersani...@gardenia.com.ph> wrote:
>> > >
>> > > Hello,
>> > >
>> > > I tried both the 1.5.0 and 2.0.0. Both returned error during 'make'
>> command. Please see below:
>> >
>> > The API between PostgreSQL and foreign data wrappers has changed
>> significantly since 9.4.  As Tom mentioned, you need to upgrade your server
>> to something in-support.
>>
>> FWIW oracle_fdw should actually build against PostgreSQL as far back as
>> 9.3
>> (though finding packages would be a fruitless endeavour); the
>> documentation
>> does explicitly mention that it will likely not build against forks which
>> are
>> not compatible with the community code [1].
>>
>> [1] https://github.com/laurenz/oracle_fdw#5-installation-requirements
>>
>> Regards
>>
>> Ian Barwick
>>
>
>
> --
>
> [image: image.png] <http://www.gardenia.com.ph/>
>
> *Jethro Elmer T. Sanidad*
> Management Information Systems
> O: +63 2 8889 8890 loc. 1354 | M: +63 9686809253
> Gardenia Bakeries Philippines Incorporated | Laguna International
> Industrial Park (LIIP) | Binan 4024 | Laguna
>
> *CONFIDENTIALITY NOTICE:* This email is confidential and subject to legal
> rights of Gardenia Bakeries (Philippines), Inc. (“GBPI”). If you received
> this email by error, you must not use or disclose any information in it and
> immediately notify the sender by return email and permanently delete this
> email (and all attachments) without any use of its contents. To the extent
> legally permitted, GBPI has no liability of any kind arising out of or in
> connection with any virus transmitted by this email, attachments, and/or
> any errors or omissions in content including transmissions through
> unauthorised use or tampering of email system and/or the integrity of the
> email being compromised. Any personal statements or opinions in this
> communication are those of the individual sender and do not reflect the
> views of GBPI. GBPI will never consent to or authorize the publication of
> defamatory statements or infringement of intellectual property. Only
> individuals authorized by GBPI’s Board of Directors may sign and/or accept
> proposals, contracts, or agreements as well as waive any legal right of
> GBPI. Any personal information in this email must be handled in accordance
> with the Data Privacy Act of 2012 of the Philippines and its implementing
> rules and regulations.
>

Re: psql \du no more showing "member of" column

[Ian Lawrence Barwick]

2023年10月13日(金) 20:47 Luca Ferrari :
>
> Hi all,
> in version 16 psql does not show anymore the "member of" group
> information when showing users with \du.
>
> The query (still working fine) in previous versions was:
>
> SELECT r.rolname, r.rolsuper, r.rolinherit,
>  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
>  r.rolconnlimit, r.rolvaliduntil,
>  ARRAY(SELECT b.rolname
>FROM pg_catalog.pg_auth_members m
>JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
>WHERE m.member = r.oid) as memberof
> , r.rolreplication
> , r.rolbypassrls
> FROM pg_catalog.pg_roles r
> WHERE r.rolname !~ '^pg_'
> ORDER BY 1;
>
>
> while now it is:
>
> SELECT r.rolname, r.rolsuper, r.rolinherit,
>  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
>  r.rolconnlimit, r.rolvaliduntil
> , r.rolreplication
> , r.rolbypassrls
> FROM pg_catalog.pg_roles r
> WHERE r.rolname !~ '^pg_'
> ORDER BY 1;
>
>
> at least, as it is shown by `psql -E`.
> I wonder why this information has been removed, I'm not able to find
> this in the documentation.

FWIW the explanation is in commit 0a1d2a7df8:

  
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a1d2a7df852f16c452eef8a83003943125162c7

and also noted in the release notes:

  https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PSQL

Regards

Ian Barwick

Re: Assistance Needed: Error during PostgreSQL Configuration

[Ian Lawrence Barwick]

2023年12月11日(月) 18:09 Ayush Vatsa :
>
> Hello, PostgreSQL community,
> I'm reaching out to you about an issue I've had while trying to configure 
> PostgreSQL by cloning its files from the GitHub repository.
> During the configuration process, I encountered an error message that 
> appeared to be related to a syntax issue within the 'config.status' file.

FWIW 'config.status' is the log file generated by 'configure'.

> Specifically, the error message reads as follows:
> ./config.status: line 486: syntax error near unexpected token `)'
> ./config.status: line 486: ` *\'*) ac_optarg=`$as_echo "$ac_optarg" | sed 
> "s/'/'''/g"` ;;'
> I have tried to resolve this on my own, but unfortunately, I haven't been 
> successful in finding a solution.
> Could you please provide guidance or assistance in resolving this issue? I 
> would appreciate any insights or suggestions you have to help me move past 
> this obstacle.
> Looking forward to your response.

You should provide, at the very least, the following information:
- the PostgreSQL version you are trying to build
- the environment you are trying to build it in

A very hand-wavy guess, but possibly your local "sed" installation is
not GNU sed?

Regards

Ian Barwick

Re: Help with "Create Extension unaccent"

[Ian Lawrence Barwick]

2024年1月14日(日) 14:53 Lan Xu :
>
> With CREATE EXTENSION unaccent, I’m getting the following:
> ERROR:  extension "unaccent" has no installation script nor update path for 
> version “1.0"
>
> But I can find it under /usr/share/pgsql/extension/unaccent001.0.sql

For recent PostgreSQL versions, the extension files are:

  unaccent--1.0--1.1.sql
  unaccent--1.1.sql

so it looks like something is not right with your installation, but
without any more
details it's not possible to say what.

Regards

Ian Barwick

Re: mystery with postgresql.auto.conf

[Ian Lawrence Barwick]

Hi

2024年4月10日(水) 20:10 Matthias Apitz :
(...)
> End of March I started to investigate the TDE extension pg_tde within
> the 16.2 server. And only this software contains this extension:
>
> # find /usr/local/sisis-pap/pgsql** | grep pg_tde
> /usr/local/sisis-pap/pgsql-16.2/lib/pg_tde.so
> /usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde--1.0.sql
> /usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde.control
>
> Today I wanted to start the 15.1 server and it failed with:
>
> 2024-04-10 11:32:32.179 CEST [14017] FATAL:  could not access file "pg_tde": 
> No such file or directory
> 2024-04-10 11:32:32.181 CEST [14017] LOG:  database system is shut down
>
> I investigated the reason and found that the pg_tde extension was
> enabled also in the 15.1 server's file postgresql.auto.conf
>
> # ls -l pos*/data/postgresql.auto.conf
> -rw--- 1 postgres postgres  88 May  7  2021 
> postgresql131/data/postgresql.auto.conf
> -rw--- 1 postgres postgres 124 Mar 28 11:35 
> postgresql151/data/postgresql.auto.conf
> -rw--- 1 postgres postgres 124 Mar 28 12:58 
> postgresql162/data/postgresql.auto.conf
>
> # cat postgresql151/data/postgresql.auto.conf
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> shared_preload_libraries = 'pg_tde'
>
> How is this possible? I only used in the 16.2 server the SQL commands:
>
> sisis=# CREATE EXTENSION pg_tde;
> sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
> sisis=# SELECT pg_tde_set_master_key('my-master-key','file');

The simplest explanation is that you (or someone), when configuring pg_tde,
accidentally executed (as per the instructions [*]):

 ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

in the 15.1 instance, rather than the 16.2 instance. This will have
resulted in the
entry in the 15.1 postgresql.auto.conf.

[*] https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file#installation-steps

Regards

Ian Barwick

Re: mystery with postgresql.auto.conf

[Ian Lawrence Barwick]

2024年4月10日(水) 21:40 Matthias Apitz :
>
> El día miércoles, abril 10, 2024 a las 09:08:56 +0900, Ian Lawrence Barwick 
> escribió:
>
> > > # cat postgresql151/data/postgresql.auto.conf
> > > # Do not edit this file manually!
> > > # It will be overwritten by the ALTER SYSTEM command.
> > > shared_preload_libraries = 'pg_tde'
> > >
> > > How is this possible? I only used in the 16.2 server the SQL commands:
> > >
> > > sisis=# CREATE EXTENSION pg_tde;
> > > sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
> > > sisis=# SELECT pg_tde_set_master_key('my-master-key','file');
> >
> > The simplest explanation is that you (or someone), when configuring pg_tde,
> > accidentally executed (as per the instructions [*]):
> >
> >  ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
> >
> > in the 15.1 instance, rather than the 16.2 instance. This will have
> > resulted in the
> > entry in the 15.1 postgresql.auto.conf.
>
> Here are my notes from the testing pg_tde:
>
> Install sisis-pap v73 and create a PostgreSQL 16.2 cluster the
> usual way, load a database dump into it (all done on
> srap21dxr1.dev.oclc.org)
>
> I followed exactly https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file
>
> psql -Usisis sisis
> psql (16.2)
> Type "help" for help.
>
> sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
>
> (PostgreSQL restart)
> ...
>
> The notes have been done by cut&paste into a text file. The psql
> was fired up against the 16.2 server as it says above. And we also
> have never two servers up at the same time.
>
> Maybe later I did it accidently against the 15.1 server from the psql
> history. I just tested it in the 15.1 server: it does not give any
> error:
>
> psql -Usisis sisis
> psql (15.1)
> Type "help" for help.
>
> sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
> ALTER SYSTEM
> sisis=#
>
> and the file gets modified :-(
>
> Why it does not give an error because the shared lib isn't there?

ALTER SYSTEM is a way of modifying the PostgreSQL configuration file
via SQL; just as when you modify it manually, changes are not applied
until you actually reload the configuration.

See: https://www.postgresql.org/docs/current/sql-altersystem.html

Regards

Ian Barwick

Re: tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database

[Ian Lawrence Barwick]

2024年5月29日(水) 6:10 Sumit Kochar :
>
> Installation of Foreign data Wrapper on EDB Postgres to connect to SQL server 
> database is not working.
>
>
>
> https://github.com/tds-fdw/tds_fdw/issues/357
>
>
>
> Please advise if this has been encountered or a workaround is available.
(...)

> [root@hostip tds_fdw-2.0.3]# make USE_PGXS=1 install
>
> /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes 
> -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  
> -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/edb/as13/include/server 
> -I/usr/edb/as13/include/internal -I/usr/libexec/edb-icu66/include/ 
> -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c 
> -o src/tds_fdw.bc src/tds_fdw.c
>
> make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found

IIRC you'll need to find and install the SCLO package for Oracle Linux
7, assuming it's
available. For CentOS it's this repository:
http://mirror.centos.org/centos/7/sclo/ .

Regards

Ian Barwick

Re: Foreign Data Wrappers

[Ian Lawrence Barwick]

2024年9月7日(土) 9:55 Gus Spier :
>
> I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer 
> to success through the documentation, youtube videos, and various google 
> resources. But I've come across a concept that intrigues me very deeply.
>
> If I understand the concepts correctly, FDW not only makes other databases 
> available, FDW also offers access to .csv files, plain text, or just about 
> anything that can be bullied into some kind of query-able order. Has anyone 
> ever tried to connect to redis or elasticache? If so, how did it work out?

There are two FDW implementations listed for Redis here:


https://wiki.postgresql.org/wiki/Foreign_data_wrappers#NoSQL_Database_Wrappers

but neither have been updated for recent PostgreSQL versions.

Regards

Ian Barwick

Re: Error creating postgres extensions

[Ian Lawrence Barwick]

2021年5月5日(水) 10:43 Tiffany Thang :
>
> Hi,
>
> I'm having trouble finding in the documentation the steps to install the 
> postgres extensions such as pg_stat_statements and pg_trgm on PG13.1/CentOS7. 
> Can someone please assist?
>
>
> postgres=# create extension pg_stat_statements;
>
> ERROR:  could not open extension control file 
> "/usr/pgsql-13/share/extension/pg_stat_statements.control": No such file or 
> directory
>
> postgres=# create extension pg_trgm;
>
> ERROR:  could not open extension control file 
> "/usr/pgsql-13/share/extension/pg_trgm.control": No such file or directory
>
>
>
> I installed PG13 via the YUM repository 
> https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7-x86_64 and then
>
> I ran "yum install postgresql13 postgresql13-server"

You need the "postgresql13-contrib" package as well.

> Also, what are the steps to update these extensions in the future when newer 
> releases are made available. For example, updating
>
> pg_stat_statements from 1.7 to 1.8?

Normally new extension versions (at least for the core contrib
modules) are provided with
each major version release, so that will be take care of when
performing a major version upgrade.

If you do ever need to update an extension, the process is:
- install new binary
- execute "ALTER EXTENSION UPDATE"
- if the extension provides a shared library, PostgreSQL will need to
be restarted to activate the new library

Regards

Ian Barwick


-- 
EnterpriseDB: https://www.enterprisedb.com

Re: I have no idea why pg_dump isn't dumping all of my data

[Ian Lawrence Barwick]

2021年5月21日(金) 20:42 Vijaykumar Jain :
>
> PostgreSQL: Documentation: 13: 37.17. Packaging Related Objects into an 
> Extension
>
> so it works as expected.
> someone would have to point to the reference wrt modification of data in 
> objects created via extension.

If you want to be able to dump data inserted into extension tables,
the tables will need
to be marked using "pg_extension_config_dump()" in the extension script,  see:

  
https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES

and also:

   https://pgpedia.info/p/pg_extension_config_dump.html


Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: ALTER DATABASE SET not working as expected?

[Ian Lawrence Barwick]

2021年9月9日(木) 16:53 Tim Uckun :
>
> This seems simple enough but it's not working.
>
> alter database dbname  set "blah.blah" = 'test_value' ;
>
> The above statement returns with no errors.
>
> select current_setting('blah.blah')
>
> The above statement returns a record with null

Open a new database session and you should see the setting.

> Also how long does the "ALTER DATABASE SET" last? Will it survive a
> shutdown and restart?

Yes.


Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: How to read an external pdf file from postgres?

[Ian Lawrence Barwick]

2022年1月12日(水) 20:16 Amine Tengilimoglu :
>
>   Hi;
>
>  I want to read an external pdf file from postgres. pdf file will exist 
> on the disk. postgres only know the disk full path as metadata. Is there any 
> software or extension that can be used for this? Or do we have to develop 
> software for it?  Or what is the best approach for this? I'd appreciate it if 
> anyone with experience could make suggestions.

By "read" do you mean "open the file and meaningful extract data from it"? If
so, speaking from prior experience, don't. And if you really have to, make sure
the source PDF is guaranteed to be in a well-defined, predictable format
enforceable by contract law and/or people with sharp pointy sticks. I have
successfully suppressed the memories of whatever it is I once had to do with
reading data from PDFs, but though the data was eventually imported into
PostgreSQL, there was a lot of mangling probably involving a Perl module (other
languages are probably available) before it got anywhere near the database.


Reagrds

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: What to do with a PAAS-pg server

[Ian Lawrence Barwick]

2024年10月31日(木) 0:32 alexander al (leiden) :
>
> Hi,
>
> we have a supplier (via our client) who has an self build PAAS-version
> of postgresql. Ok, you would say, that's fine. But, there is always an
> but, we think the settings are not quite ok. We really want to know how
> much memory etc there is on that server. So we can recommend the
> recommended settings. Is there a way to get those information on the
> server itself from psql?

If you mean you e.g. only have access to psql on the server in question,
but want to know more about the server itself (i.e. not the PostgreSQL
settings), then you might have luck poking around with the pg_read_file()
function, e.g.:

SELECT pg_read_file('/proc/meminfo');

Note you will need database superuser permission to execute pg_read_file(),
or for non-superusers be granted EXECUTE permission on it.

Regards

Ian Barwick