passing a pointer from a BackgroundWorker to a DynamicBackgroundWorker

Hi all, I've a doubt about background workers. I've a worker (loaded by a module via shared_preloaded_libraries) should register a dyanmic background worker per database, in a manner similar to what autovacuum does. I would like to pass to these dynamic background workers a pointer to a custom stru

Re: xmax not zero?

On Mon, Oct 30, 2023 at 1:54 PM Guillaume Lelarge wrote: > There are many reasons for a non-zero value: row updated or deleted in a > rollbacked transaction, row updated or deleted in a current transaction, row > locked by a SELECT FOR UPDATE, and perhaps others I don't remember right now. > Ye

xmax not zero?

Hi all, I have a table that presents an xmax not zeroed outside of a transaction block, and it does not look normal to me. I have no idea about how this happened, it is one of my "toy" PostgreSQL virtual machines. But how to dig an better understand why there is an xmax with a non-zero value? I've

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

On Fri, Oct 13, 2023 at 3:04 PM hubert depesz lubaczewski wrote: > > On Fri, Oct 13, 2023 at 01:39:17PM +0200, Luca Ferrari wrote: > > 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 doc

psql \du no more showing "member of" column

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, A

why generated columsn cannot be used in COPY TO?

Hi all, I'm wondering why in COPY TO (file or program) I cannot use generated columns: since I'm pushing data out of the table, why they are not allowed? Example: testdb=# CREATE TABLE test( pk int generated always as identity primary key , ts timestamp default current_timestamp , month int gener

Re: debugger from superuser only.... why?

On Wed, Sep 27, 2023 at 1:30 PM Alexander Petrossian wrote: > > > 25 сент. 2023 г., в 17:28, Tom Lane написал(а): > > Alexander Petrossian writes: > I am wondering why is this, why not allow debugging for non-privileged > users? > > Even if there's a way to restrict > > debugging conn

Re: debugger from superuser only.... why?

On Fri, Sep 22, 2023 at 9:05 PM wrote: > > hi, > the first steps to use debugger: > 1. modify config file > 2. restart server > ... > i would say, enough reasons to be superuser or not? Well, the above are not privileges "to use" the debugger, rather "to configure" the debugger. Luca > >

Re: How to fork pg_dump or psql w/o leaking secrets?

On Fri, Sep 22, 2023 at 3:24 PM Dominique Devienne wrote: > I'm talking of replacing using my tool then psql, with using my tool that > forks psql. I thought you were talking about forking the source code to write your own psql, sorry about that. It is clear to me that you mean Unix fork(2) and

Re: debugger from superuser only.... why?

On Fri, Sep 22, 2023 at 2:00 PM Alexander Petrossian (PAF) wrote: > Some mail list you would suggest, Luka? > pgadmin mailing list, or ask EDB somehwere. >> >> > SELECT * FROM pldbg_create_listener(); >> uh oh, it mentions a listener...then there must be some "sender" >> somehwere, that probably

Re: debugger from superuser only.... why?

On Fri, Sep 22, 2023 at 1:28 PM Alexander Petrossian wrote: > SELECT * FROM pldbg_set_global_breakpoint(1, 65695, -1, NULL); > > ERROR: must be a superuser to create a breakpoint > > > I am wondering why is this, why not allow debugging for non-privileged users? Again, I'm suspecting that this de

Re: debugger from superuser only.... why?

On Wed, Sep 20, 2023 at 9:32 AM Александр Петросян (web) wrote: > > Checked few sources, can not seem to find reasoning behind this limit: > > > You must have superuser privileges to use the debugger. > It means database superuser. > What is the reason? I suspect the debugger will need to open

Re: How to fork pg_dump or psql w/o leaking secrets?

On Fri, Sep 22, 2023 at 12:13 PM Dominique Devienne wrote: > So my question is how I pass the password my tool already own, to the forked > PSQL, > w/o that password leaking. I could pass it on the command-line, but that > would be leaking > it to the `ps` command (and in various other places).

Re: How to fork pg_dump or psql w/o leaking secrets?

On Thu, Sep 21, 2023 at 7:46 PM Dominique Devienne wrote: > > Hi. To administer our PostgreSQL-based system, we have custom tools > to manage the schemas, load data, etc... Including a versatile CLI tool. > > But that tool is special purpose, while sometimes we want/need the general > purpose PSQL

Re: pg_visible_in_snapshot clarification

On Mon, Aug 28, 2023 at 8:34 PM Mike Roest wrote: > I think this has to do with the pg_current_snapshot not showing the > transaction in the xip_list which appears to be empty on both C1 (in the > transaction) and C3 on the replica. However C2 pg_current_snapshot() does > show C1 xactid as in

Re: PL/Perl function signatures

On Mon, Aug 28, 2023 at 9:53 AM Luca Ferrari wrote: > > testdb=# create or replace function f( a int ) > returns text > as $$ > use feature 'signatures'; shame on me! Clearly this cannot work because at the 'use' stage the function is already running, so the

PL/Perl function signatures

Hi all, is there any way to exploit sub signatures in PL/Perl? The following is not working, I suspect it is not possible due to the way PL/Perl compiles the code. testdb=# create or replace function f( a int ) returns text as $$ use feature 'signatures'; elog( INFO, "Perl version $^V" ); elog(I

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

On Fri, Aug 25, 2023 at 5:49 PM duc hiep ha wrote: > > Hi, > > I've just found the root cause! In this command: > ora2pg -c ora2pg.conf -t COPY -a tablename > I have created and changed the name of table in postgres. It will not work > like that if i don't put replace_table option in config file.

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

On Thu, Aug 24, 2023 at 6:19 PM duc hiep ha wrote: > [2023-08-24 18:07:14] Collecting 0 tables information in DBA_TABLES took: 0 > wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) I suspect there is a permission problem here. Are you really sure this same configuration works in offline mode?

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

On Thu, Aug 24, 2023 at 3:49 PM duc hiep ha wrote: > > do i have to completely delete the Ora2pg and install it again!? or some > other softaware running on the computer have prevented this transfer's > process. I don't think so. Does the -d flag helps in finding out the problem? Luca

Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly

On Thu, Aug 24, 2023 at 1:51 PM duc hiep ha wrote: > > no data have been transfered to postgres. it shows: 0/0 tables(100%) end of > scanning. > Even if asking only for the table structure? Can you perform an offline migration (i.e., saving to file)? I would suggest to ask on ora2pg support. L

Re: suggestion about time based partitioning and hibernate

On Wed, Jul 19, 2023 at 6:45 PM Alvaro Herrera wrote: > > Therefore I suggest to avoid doing > that. Either look at some other partitioning scheme that doesn't > involve adding columns to the primary key, or disregard partitioning for > this table entirely. What do you mean by "other partitionin

suggestion about time based partitioning and hibernate

Dear all, I'm looking for ideas here, and it could be someone already stepped into declarative partitioning of an existing database where Hibernate (a Java ORM) handles the tables. The situation is as follows: create table foo( id primary key, a_date date, ... ); Now, the trivial way to partition

connecting to localhost -> ::1 client_addr ?

Hi all, this may be tribial, but I'm seeing connections from ::1 in pg_stat_activity, and I've never realiuzed that psql converts "localhost" in IPv6. Is there a way to "force" the hostname localhost to appear as IPv4 or am I missing something? I've tested it also with a Perl program, and specifyin

Re: what happens if a failed transaction is not rolled back?

On Mon, Apr 24, 2023 at 5:37 PM Siddharth Jain wrote: > i am asking: what happens if a failed transaction is not rolled back? A transaction either terminates by a commit or by a rollback. A failed transaction is an opened transaction that can be terminated only by a rollback. If you don't rollbac

Re: Sequential scan faster than index

On Tue, Feb 21, 2023 at 10:30 AM Arthur Ramsey wrote: > > I'm trying to figure out why a sequential scan is out performing. I've tried > psql 13.7, psql14.6 and REINDEX. The REINDEX didn't help. This is on an RDS > instance that's a db.m5.large (2 * vCPU, 8 GB memory) with 200 storage on io1

MERGE output doubt

Hi all, I'm experimenting with the new MERGE command, but I'm not getting the output total count. From the docs : "The total_count is the total number of rows changed ". This is my simple setup: testdb=> table scores; pk | name | score +--

primary_conninfo and restore_command ?

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 a

max_connections different between primary and standby: is it possible?

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 d

ANALYZE, pg_class.xmin && pg_class.reltuples

Hi all, I've a doubt about running ANALYZE and inspecting pg_class.xmin and pg_class.reltuples. Let's create a simple table (note, I'm the only user on this instance): testdb=> CREATE TABLE fizz AS SELECT v FROM generate_series( 1, 100 ) v; SELECT 100 testdb=> SELECT relpages, reltuples, xmin, age

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 3:30 PM Achilleas Mantzios wrote: > You mean like trying to correlate pg_class.xmin with some timestamp via > track_commit_timestamp or other means? Pretty much yes: since pg_class and pg_attribute comes to my mind. Luca

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 3:09 PM hubert depesz lubaczewski wrote: > You could add event triggers to achieve similar functionality. > https://www.depesz.com/2012/07/29/waiting-for-9-3-event-triggers/ > and > https://www.postgresql.org/docs/current/sql-createeventtrigger.html Thanks, I was ware of

get last timestamp of table ddl

Hi all, I think I already know the answer, however I came across this table in Oracle that has two columns that triggered my attention: CREATED and LAST_DDL_TIME. Apart from being dates (in the Oracle way), they s

Re: Can't locate Ora2Pg.pm while executing export_schema.sh

On Tue, Aug 17, 2021 at 4:34 PM Mladen Gogala wrote: > > You should locate Ora2Pg.pm using "find" like this: > > [mgogala@umajor ~]$ find /usr/local -name Ora2Pg.pm > /usr/local/share/perl5/5.32/Ora2Pg.pm Sounds like he is using strawberry Perl, that means he is on windows. Things could be differ

Re: Help with my MacOS PostgreSQL 12 installation

On Tue, Aug 17, 2021 at 6:21 PM Michael White wrote: > 1. PostgreSQL server is not started and I am having trouble figuring out how > to start it because /Library/PostgreSQL/12 does not exist. (don’t know why) that *could be* the reason why it does not start! > 3. Don’t remember how I installe

help with fmgr

Hi all, I'm trying to understand how fmgr works, and I've written a very simple module that checks if functions f_test, f_test2 (defined by myself in SQL) or hashtext (internal) needs fmgr, and in the case print out a message when the fmgr is invoked. I've two main doubts: - is fmgr working for non

Re: autovacuum worker started without a worker entry

On Thu, Aug 5, 2021 at 6:27 PM Vijaykumar Jain wrote: > postgres/varsup.c at master · postgres/postgres (github.com) > I think, this block when it is about to assign the next xid, it does the > math, and triggers an autolauncher start. > I might be wrong, I did not run a backtrace though :) > >

Re: pgcrypto - real life examples to encrypt / decrypt

On Fri, Aug 6, 2021 at 8:27 PM Reid Thompson wrote: > > someone mentioned an alternative earlier that they couldn't remember -- i > think it may have been pgsodium that they were thinking of > https://github.com/michelp/pgsodium > Yes, thanks. For some reason I was remembering "selenium" someth

autovacuum worker started without a worker entry

Hi all, I occasionally see the message "WARNING: autovacuum worker started without a worker entry" in the logs. >From what I can see here , the launcher forked a worker and in the meantime the launcher deci

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Mon, Aug 2, 2021 at 10:31 PM Dhanush D wrote: > > Sharing more context on this: if I run > > /usr/pgsql-9.6/bin/pg_dump -d -t public.auth_permission; > > I see multiple "CREATE SEQUENCE public.auth_permission_id_seq" commands. > This seems unusual. > Seems there is either a problem on the c

Re: pgcrypto - real life examples to encrypt / decrypt

On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma wrote: > My question is, can I use the gpg public/secret key instead of the 'Secret > password' in above PGP_Sym_encrypt/decrypt? I can create a wrapper function > to read the public/secret keys to hide it from appearing as clear text. I think you ar

Re: Unexpected block ID found when reading data

On Tue, Aug 3, 2021 at 1:16 PM Gilar Ginanjar wrote: > Is there any small chance I can view/save/restore my data? I desperately need > it. > I guess there is not much left to do. I will try doing a pg_restore -t one table at a time to limit the damage, assuming that --section=data is not working

Re: Unexpected block ID found when reading data

On Tue, Aug 3, 2021 at 4:49 AM Gilar Ginanjar wrote: > pgrestore command: > pg_restore -U myuser -j8 -d mydb dbdump.backup > As a desparate approach I would try to use options -L and -l to extract objects from the backup and restore a small subset of them, in order to find out where the error is.

Re: pgcrypto - real life examples to encrypt / decrypt

On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma wrote: > > Dear Experts, > > Could you please share some real life examples of using pgcrypto in > production? > > I am planning to use it in our environment and wondering what could be the > best practice for its use. It is not clear what you are go

Re: dealing with dependencies

On Fri, Jul 16, 2021 at 2:43 PM Markhof, Ingolf wrote: > I wonder how you deal with it in a professional way. Sounds like some type of > "make" (that UNIX tool dealing with dependencies in the context of e.g. > programming in C) would be helpful... > I see this question coming out very often, a

Re: pg_dumpall with individual output files per database?

On Thu, Jul 15, 2021 at 7:27 PM Francisco Olarte wrote: > It would probably complicate it, and dumping a whole cluster using > something like pg_dumpall -g for the globals plus a loop over the > databases using something like pg_dump -Fc ( which I would always > recommend over plain sql format ) i

Re: PostgreSQL 9.2 high replication lag

On Fri, Jul 16, 2021 at 12:38 AM Lucas wrote: > However, it hasn't changed and the replication lag is still high all the time. > Since the documentation states that max_standby_streaming_delay "[...]it is the maximum total time allowed to apply WAL data once it has been received from the primary

Re: pg_wal lifecycle

On Tue, Jul 13, 2021 at 3:43 PM Peter Eisentraut wrote: > Recycling in this context just means that instead of creating a new WAL > file for new WAL traffic, it reuses an old file. So if you have WAL > files 5, 6, 7, 8, 9, and you know that you don't need 5 and 6 anymore, > when you need to start

statistic target and sample rate

Hi all, I've a doubt about the sampling rate about the statistic target. Documentation states that: "The largest statistics target among the columns being analyzed determines the number of table rows sampled to prepare the statistics. Increasing the target causes a proportional increase in the tim

pg_wal lifecycle

Hi all, I'd like to see if I get it right about pg_wal: it grows up to pretty much max_wal_size, at which point a checkpoint is triggered. If the server triggers a timed checkpoint before the pg_wal is at max_wal_size, the system recycles the wals thus keeping the pg_wal size lower than max_wal_siz

Re: pg_upgrade as a way of cloning an instance?

On Thu, Jul 8, 2021 at 2:30 AM Bruce Momjian wrote: > Also, pg_upgrade will throw an error if copying from one version to the > same version if there are tablespaces since the tablespace directory > name is the catalog version name. A very good point to discourage this usage! Thanks, Luca

Re: pg_upgrade as a way of cloning an instance?

On Wed, Jul 7, 2021 at 10:22 AM Peter Eisentraut wrote: > Yeah, seems pretty pointless. You can just copy the data directory > directly and get the same effect. pg_upgrade basically does a data > directory copy plus some extra stuff to convert the system catalogs > between versions, and if you d

pg_upgrade as a way of cloning an instance?

Hi all, someone pointed me out that pg_upgrade can be used to do a clone of the database, specifying the same binaries such as pg_upgrade -B /usr/pgsql-13/bin -b /usr/pgsql-13/bin -D /data/clone -d /data/src I tested it and it seems to work, even if I don't see any point in running it (and most n

Re: order by

On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain wrote: > you can run an explain analyze to check what is going on, > when you provide a table in query in the order by clause, it is > ordered by cols of that table in that order. Clever, thanks! I also realized that this "table to tuples" expansio

order by

Hi all, this could be trivial, but I found as an accident the following: pgbench=> create table t as select v from generate_series( 1, 2 ) v; SELECT 2 pgbench=> select * from t order by foo; ERROR: column "foo" does not exist LINE 1: select * from t order by foo;

Re: disabling seqscan not using primary key index?

On Sat, May 15, 2021 at 4:40 PM David Rowley wrote: > > The answer is fairly simple, the planner just never considers using > the primary key index as there are no possible cases where it would be > useful. Does this mean that any UNIQUE constraint on the table is subject to the same consideratio

disabling seqscan not using primary key index?

Hi all, doing a little and trivial experiment, I decided to populate a table with a primary key (and thus an automatically generated btree index): testdb=# create table foo( pk serial primary key, i int ); CREATE TABLE testdb=# insert into foo( i ) select v from generate_series( 1, 100 ) v; IN

enable_seqscan to off -> initial cost 10000000000

Hi all, this could be trivial, but I would like an explaination: if I turn off sequential scans on a table without indexes, the same access plan is increased by a 100 factor. I suspect this is a warning for me to remind that something is misconfigured, or is there anothe reason? testdb=# c

Re: questions about wraparound

On Fri, Apr 2, 2021 at 10:29 AM Jehan-Guillaume de Rorthais wrote: > > On Thu, 18 Mar 2021 09:56:16 +0100 > Luca Ferrari wrote: > [...] > > Therefore my question is: shouldn't autovacuum be able to freeze other > > tables/databases? I mean, the wraparound problem in

Re: questions about wraparound

On Mon, Mar 29, 2021 at 7:12 PM Laurenz Albe wrote: > I didn't follow the rest of the thread, but autovacuum should handle > those databases and advance their "datfrozenxid". That did not happen. In short: I turned off autovacuum globally, then in a database I consumed a lot of xid while keeping

Re: questions about wraparound

On Mon, Mar 29, 2021 at 11:14 AM Laurenz Albe wrote: > > If there is no activity on a database, its "datfrozenxid" stays > the same. So, as transaction IDs are consumed, it is getting older > automatically. That means that even inactive databases will receive > an anti-wraparound vacuum occasion

Re: questions about wraparound

On Mon, Mar 22, 2021 at 8:56 AM Luca Ferrari wrote: > backend> select datname, age( datfrozenxid), current_setting( > 1: datname = "template0" (typeid = 19, len = 64, typmod > = -1, byval = f) > 2: age = "2138438218" (typeid =

Re: questions about wraparound

On Sat, Mar 20, 2021 at 12:07 PM Luca Ferrari wrote: > I suspect freezing is doing it "totally" for a idatistemplate > database, even if I don't understand why. I can confirm that freezing a template database is done by means of setting it age to zero. I've set the dat

Re: questions about wraparound

On Thu, Mar 18, 2021 at 12:14 PM Luca Ferrari wrote: > testdb=> select datname, datfrozenxid, age(datfrozenxid) from pg_database; > datname | datfrozenxid | age > ---+--+-- > postgres | 3318163526 | 5002 > backupdb | 3318163526 | 500

Re: questions about wraparound

On Thu, Mar 18, 2021 at 9:56 AM Luca Ferrari wrote: > > What puzzles me is that I'm somehow "locking" the testdb.wa table (by > inserting a tuple every 20 minutes), so all other tables and databases > are free to be frozen by an emergency autovacuum. And I was expect

questions about wraparound

Hi all, I'm doing some experiments on a cluster to see what happens at xid wraparound, and I'm approaching it. There is no activity in any database but testdb. Scenario: I've a procedure that is consuming all xids, while another connection is inserting a tuple every 20 minutes or so in a table, jus

Re: cannot promote after recovery for PITR

On Mon, Feb 22, 2021 at 3:42 PM Luca Ferrari wrote: > If, instead, I do pg_wal_replay_resume(), the server is promoted (of > course not at the PITR I want). > Am I missing something? Apparently I was missing caffeine and confused the usage of the functions. pg_wal_replay_resume was w

cannot promote after recovery for PITR

I'm running 12.5, I've restored a backup copy (from barman) to a specific restore point. The system has gone into pause, and I've checked that everything is as I was expecting. Now I'm unable to promote the cluster: testdb=# SELECT pg_is_wal_replay_paused(); pg_is_wal_replay_paused --

Re: how does PostgreSQL determine how many parallel processes to start

On Fri, Feb 19, 2021 at 10:43 AM Laurenz Albe wrote: > At execution time, PostgreSQL will use as many of the planned workers > as are currently available (max_parallel_workers). Thanks, but just to make it clear, assuming I execute almost simultanously two identical queries that can be therefore

how does PostgreSQL determine how many parallel processes to start

Hi all, I know that parallel processes can be limited by max_parallel_workers_per_gather and max_parallel_workers, as well as the condition to consider a parallel plan is min_table_scan_size (and index). But I would like to understand, once a table has been considered for a parallel plan, and there

when is pg_stat_archiver.stats_reset modified?

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, b

pg_stat_progress_vacuum empty when running vacuum full

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 t

when is useful min_wal_size?

Hi all, this may sound trivial, but in my opinion min_wal_size is useful only when the cluster is initialized or the wals are reset. Am I wrong? Luca

Re: two questions about toast

On Sun, Nov 15, 2020 at 6:45 PM Adrian Klaver wrote: > Database --> Table --> TOAST table. Thanks, I was not sure about. > > > 2) while executing a query against toasted values I got this debug > > message that I don't know what is meaning > > This happened when you where doing the REINDEX DATABA

two questions about toast

Hi all, I've a couple of doubts about toast: 1) is a REINDEX DATABASE hitting also toast tables or they need to be reindexed manually? 2) while executing a query against toasted values I got this debug message that I don't know what is meaning DEBUG: building index "pg_toast_33875_index" on table

rejecting "interactive" connections

Hi all, I'm just wondering if there is a way to discriminate a connection opened from an application server from one opened by an interactive client (e.g., psql) using the same credentials: the aim is to reject connections established from an interactive client rather than an application server. I

Re: Index

On Fri, Oct 25, 2019 at 7:35 AM Sonam Sharma wrote: > > I have created indexes with certain set of columns. Now I want to remove one > of the column and add new column. Can this done without dropping the index? If you are going to rename the column the answer is yes. If you are going to drop th

Re: Quere keep using temporary files

On Fri, Oct 25, 2019 at 10:23 AM rihad wrote: > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp92452.1079", size 166518 > STATEMENT: DELETE FROM "foo" WHERE ((col1, col2, col3) in (select col1, > col2, col3 from foo_xml_v2(''))) AND > "foo"."col_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)

LocalTransactionId vs txid_current

I'm exploring DTrace, and I thought that LocalTransactionId as described in would have a relationship with txid_current(), but apparently I'm not getting it: template1=# begin; template1=# select txid_current(); txid_current --

Re: Postgres Point in time Recovery (PITR),

On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram wrote: > One more questions is, how backups are useful if we have streaming > replication . As I know, we can promote the standby as primary in case of > disaster at primary side. Do we need to schedule backups if we have streaming > replication? Let'

UTC-6 or UTC+6?

I'm a little confused by trying to get the time at a different timezone, formerly UTC-6: template1=# select '2019-10-22 16:00:00' at time zone 'Europe/Rome' as my_time , '2019-10-22 16:00:00' at time zone 'America/Denver' as what_should_be, '2019-10-22 16:00:00' at time zone 'UTC-6' as utc_minus_6

Re: stable for each row before insert trigger

On Fri, Oct 18, 2019 at 11:18 AM Олег Самойлов wrote: > > STABLE indicates (It is inappropriate for AFTER triggers that wish to > > query rows modified by the current command.) > > So, STABLE is inappropriate for such trigger, but is appropriate for BEFORE > trigger? > Well, a before trigg

Re: Postgres Point in time Recovery (PITR),

On Fri, Oct 18, 2019 at 10:30 AM Andreas Joseph Krogh wrote: > We use barman (https://www.pgbarman.org/) for continuous streaming backup and > I had to restore from it once, and it went like this: Just for the records, here's an example of restore with pgbackrest: % sudo -u postgres pgbackrest

Re: Postgres Point in time Recovery (PITR),

On Fri, Oct 18, 2019 at 7:59 AM Daulat Ram wrote: > Can you please share some ideas and scenarios how we can do the PITR in case > of disaster. In order to be able to do PITR you need: - a base backup of your database - WALs from the backup going on See

Re: stable for each row before insert trigger

On Fri, Oct 18, 2019 at 9:16 AM Tom Lane wrote: > > =?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= writes: > > According to documentation the words "VOLATILE, STABLE, IMMUTABLE" is > > somehow useful with trigger functions, for instance mentioned that the > > AFTER INSERT trigger should be VO

Re: Vacuum very big table - how the full vacuum works in background/internally?

On Thu, Oct 17, 2019 at 5:10 PM Durumdara wrote: > Please help me, how the PGSQL full vacuum works internally? (1., 2. case, or > something else) > As far as I know a VACUUM FULL will rewrite the whole table without inserting (and therefore deleting later) not visible tuples. I would be quite su

Re: PostgreSQL memory usage

On Wed, Oct 16, 2019 at 6:30 PM Alexander Pyhalov wrote: > I see that at some point several postgresql backends start consuming about 16 > GB RAM. If we account for shared_buffers, it meens 4 GB RAM for private > backend memory. How can we achieve such numbers? I don't see any long-running > (

Re: Regarding db dump with Fc taking very long time to completion

On Wed, Oct 16, 2019 at 11:27 AM Durgamahesh Manne wrote: > Is there any way to reduce dump time when i take dump of the table which has > 148gb in size without creating partiton on that table has 148gb in size ? I would at least try a parallel dump: should it be -j option to pg_dump. I'm not s

Re: Securing records using linux grou permissions

On Tue, Oct 15, 2019 at 6:07 PM David Gauthier wrote: > Users are going to be working with data through perl/DBI scripts which > currently connect using a generic role with hardcoded password in the connect > string. Access will be select/insert/update/delete We need to tighten up > security

Re: Minimum privilege for Backup and replication

On Thu, Oct 10, 2019 at 1:26 PM Timmy Siu wrote: > Now, my question is - > What is the Minimum Privilege of a pgsql Backup or Replication user? It is not clear what your question really is. If you are thinking about a "database user with backup grant", there is no thing like that in PostgreSQL. I

Re: Event Triggers and Dropping Objects

On Mon, Oct 7, 2019 at 10:12 PM Bruce Momjian wrote: > Do the Postgres docs need improvement here? I don't know, but I would probably add a flag column in the firing matrix to indicate when the related function will return a null tuple. Luca

Re: Event Triggers and Dropping Objects

On Fri, Oct 4, 2019 at 10:38 PM Miles Elam wrote: > > The event trigger firing matrix lists tags like DROP TABLE and DROP FUNCTION > are listed below the ddl_command_end event, but when I created a basic audit > table and event trigger, they don't seem to fire. I know sql_drop exists, but > aga

Re: incoherent dead tuples between pg_stat_user_tables and pgstattuple?

On Fri, Sep 27, 2019 at 4:43 PM Tom Lane wrote: > > I'm not suggesting any specific scenario, because you haven't presented > any evidence as to when those counts became off. I'm just saying that > there are plenty of possible mechanisms for them to become so. So far autovacuum seems the main r

Re: incoherent dead tuples between pg_stat_user_tables and pgstattuple?

On Fri, Sep 27, 2019 at 3:52 PM Tom Lane wrote: > n_live_tup/n_dead_tup should not be thought to be better than > approximations. Most operations adjust them only incrementally, > and messages to the stats collector can get dropped entirely > under heavy load, causing deltas to go missing. There

Re: pgq is one of the most underrated pg related stuff

On Wed, Sep 25, 2019 at 12:18 PM Миша Тюрин wrote: > // another underrated was multicorn. Is it still alive? Apparently it is , even if it is breathing slow . Luca

incoherent dead tuples between pg_stat_user_tables and pgstattuple?

Hello, running 11.5 I've some misconception about pgstatuple: it seems it lags behind normal statistics. I've almost double a table with an update. The result is: testdb=# select n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze from pg_stat_user_tables where relname = 'foo'; -[ RECORD 1 ]

Re: unable to drop index because it does not exists

On Mon, Sep 23, 2019 at 2:23 PM Ron wrote: > Try specifying the schema name: Yeah, shame on me, I always forget the schema for indexes! Thanks, Luca

Re: pg_receivexlog or archive_command

On Mon, Sep 23, 2019 at 10:55 AM Andreas Kretschmer wrote: > you can use both of them, and you should consider "Barman". If I remember well Barman uses pg_receivexlog when streaming, and archive_command when doing a "normal" backup. Also pgbackrest is another good tool for backup. The idea here s

unable to drop index because it does not exists

Hi, running 11.5 I've got a partitioned table where I want to destroy an index (on a column that has nothing to do with the partition): respidb=# \d+ respi.root Column|Type | --+-+- sen_id | integer

Re: install pgcrypto module to existing postgreSQL

On Tue, Sep 17, 2019 at 11:19 PM Pavan Kumar wrote: > once configure is done, used make and make install to install postgres. cd contrib && make && make install that should work. Luca

  1   2   3   >