PostgreSQL backup issue

[Jayadevan M]

Hello all,

I have a PostgreSQL backup script which executes daily. The backup creates
no output on "some" days - no pattern observed yet. There is no space
issue.
Here are the relevant lines from the shell script.

mkdir $bdir
echo "Backup began at  "  `date` | tee -a $logfile
pg_basebackup --checkpoint=fast --xlog-method=fetch -v -P -h xx.xxx.xx.xx
-U replication -R -D  $bdir  | tee -a $logfile
echo "Backup completed at  "  `date` | tee -a $logfile

The directory $bdir does get created. But it remains empty. The echo before
and after the backup is writing to $logfile.
Everything that happens on the database gets logged in PGLOG file. Even the
connection request by replication user is not there in PGLOG file on the
days when the backup doesn't work. Best way to trouble-shoot this? Also,
the output from pg_basebackup does not get logged in $logfile even on those
days when the backup works fine. How can I get the output into the logfile?

Regards,
Jayadevan

Re: PostgreSQL backup issue

[Jayadevan M]

>
>
> I would conclude that pg-basebackup is placing its output in stderr
> instead of stdout then...
>
>
>
Thanks. Modifying the last potion to '>>$logfile 2>&1' worked.

Regards,
Jayadevan

Re: What am I doing wrong here?

[Jayadevan M]

Hi,


On Thu, Dec 26, 2019 at 7:06 PM stan  wrote:

>
>
> _bom_name_key  = ( SELECT
> project_bom_key
>FROM inserted )
> ;
>

Try rewriting
_bom_name_key  = ( SELECT
project_bom_key
   FROM inserted )

asSELECT project_bom_key into _bom_name_key
   FROM inserted ;
Regards,
Jayadevan

Re: Need support on tuning at the time of index creation

[Jayadevan M]

On Fri, Jan 31, 2020 at 3:22 PM Sandip Pradhan 
wrote:

> Hi Ron,
>
> Thanks for your time.
>
> We are using the version 9.5.9.14.
>


May be you could try tweaking maintenance_work_mem?

Regards,
Jayadevan

Re: Oracle vs. PostgreSQL - a comment

[Jayadevan M]

On Sat, May 30, 2020 at 5:51 PM Paul Förster 
wrote:

> Hi,
>
> I know, this list is not for this, but I just couldn't resist. Please
> forgive me.
>
> Being an Oracle DBA for two decades now (back then starting with Oracle
> 8.0.5) and only doing PostgreSQL since version 10.3, I feel compelled to
> share some of my experiences with both.
>
> The Stack Overflow survey results are interesting - try the top 2 in Most
Dreaded databases.
https://insights.stackoverflow.com/survey/2019#technology-_-most-loved-dreaded-and-wanted-databases


Regards,
Jayadevan

Re: \COPY command and indexes in tables

[Jayadevan M]

>
> We load large tables in some customer installation (some millions of rows)
> from file with:
>
> TRUNCATE TABLE tableName ;
> \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' )
>
>
May be you could also make sure that loading actually stopped, by checking
the size of the data directory. In another session, you could try
watch du -h  -s 

Regards,
Jayadevan

Re: How to check if a materialised view is being updated?

[Jayadevan M]

>
> So I’m looking for a way to identify if the refresh process is finished or
> if it’s still running  - preferably without having to wait for timeout by
> querying a locked materialized view.  But by e.g. using the system tables
> or otherwise.
>
>
>
> Can anybody suggest some pointers on how to do this?
>

Maybe pg_stat_activity will have the refresh query?

Regards,
Jayadevan

Re: Postgres Analog of Oracle APPEND hint

[Jayadevan M]

>
> Using PostgreSQL 13.1
>
> I am new to PostgreSQL transitioning from Oracle.  One of the many Oracle
> tricks I learned is that large inserts can be sped up by adding the direct
> path load hint /*+APPEND*/ .  I am faced with having to perform many large
> inserts (100K->100M rows) in my PostgreSQL database.
>
> My questions are:
>
>- Is there something comparable within the PostgreSQL community
>edition product?
>- Are my only options to un-log the table and tune instance memory
>parameters?
>
>
> I remember trying this some time ago. It is not part of the PG community
edition. Still, worth a look -
https://ossc-db.github.io/pg_bulkload/pg_bulkload.html


Regards,
Jayadevan

Metadata and data lineage tool

[Jayadevan M]

Hi all,

We use PostgreSQL as. our primary data persistence layer and are looking
for a tool to document the database as well as capture additional info.
Looking for suggestions.
The tool should be able to scan the database (PostgreSQL) and generate a
list of entities and attributes and persist this info.  We would need to
add tags to the columns (like "PII data").
We also need to manually add information about other data persistence
layers (Redis). It will be great if we can map an attribute in Redis to a
column in PostgreSQL so that we can visualise the data flow.
Any suggestion will be highly appreciated. Open source solutions preferred.

Best Regards,
Jayadevan

log_statement setting

[Jayadevan M]

Hello all,

A doubt on the setting  'log_statement'. Is it possible to set this at
database/user level?
I get 'all' when I try
select * from pg_settings where name = 'log_statement'
Does this mean all statements by any user on any database in that server
are getting logged? This is for auditing purposes.

Regards,
Jayadevan

Re: log_statement setting

[Jayadevan M]

On Wed, Aug 18, 2021 at 9:36 PM Bruce Momjian  wrote:

> On Wed, Aug 18, 2021 at 09:34:08PM +0530, Jayadevan M wrote:
> > Hello all,
> >
> > A doubt on the setting  'log_statement'. Is it possible to set this at
> database
> > /user level?
>
> Sure, ALTER USER/DATABASE ... SET.
>
Thank you. Follow up question- If it is set to different values for
different users/databases, how can I get those values?  For example,
I have a server with 4 databases. If I just query pg_settings, I get only
one value.

>
> > I get 'all' when I try
> > select * from pg_settings where name = 'log_statement'
> > Does this mean all statements by any user on any database in that server
> are
> > getting logged? This is for auditing purposes.
>
> Yes.
>
Thanks.

Re: log_statement setting

[Jayadevan M]

On Thu, Aug 19, 2021 at 11:41 AM Julien Rouhaud  wrote:

>
> The value you see on pg_settings is the one that's selected for your
> current connection, based on the database/role you used.
>
> You can use \drds in psql to see the various configurations, or query
> the pg_db_role_setting table.
>
Thank you.  Pointing to pg_db_role_setting was very helpful. I was able to
get the data I wanted.
Regards,
Jay

String comparison fails for some cases after migration

[Jayadevan M]

Hello all,

We moved our PostgreSQL database from one hosting provider to another using
pgbackrest.  In the new environment, some comparison operations  were
failing.  The issue was fixed by running an update. But I am trying to find
out what would have happened.

select * from accounts where email = 'someem...@gmail.com'; -- failed for
some email ids even though there were records.

select * from accounts where lower(trim(email)) <> email; -- fetched no
records.

select * from accounts where email::bytea = 'someem...@gmail.com'::bytea; -
worked for those records where comparison was failing.

update accounts set email = trim(lower(email)); -- fixed the issue.

Source database was PG 13.2, target 13.3.

Any suggestions will be appreciated - not sure if other tables/columns are
affected.

Best Regards,
Jayadevan

Re: String comparison fails for some cases after migration

[Jayadevan M]

On Wed, Oct 27, 2021 at 11:49 AM Julien Rouhaud  wrote:

>
>
> Most likely you had a different version of the glibc or ICU libraries
> on the new system, which lead to your indexes on collatable datatypes
> partially corrupted.  See https://wiki.postgresql.org/wiki/Collations
> for more details.
>
> You can issue a REINDEX for each impacted index, or a database-wide
> REINDEX.
>

Tried creating  a new env with the same approach - just reindexing solved
the problem. Thanks a lot.

Regards,
Jayadevan

Re: How can a Postgres SQL script be automatically run when a new table turns up?

[Jayadevan M]

When a user load a new table in the Postgres System?  Can a script
> automatically detect it and run?
>
> Are you looking for Even triggers?
https://www.postgresql.org/docs/current/event-triggers.html

Regards,
Jay

Re: PostgreSQL logical replication

[Jayadevan M]

On Tue, Nov 12, 2024 at 7:47 PM Ron Johnson  wrote:

> The documentation tells you, down in the Notes section.
>
> https://www.postgresql.org/docs/16/sql-createpublication.html
>
Thank you.

PostgreSQL logical replication

[Jayadevan M]

Hello all,

I am using PostgreSQL 16.4. I tried to set up logical replication with
"postgres" user and all worked fine.  Then I created a new user
(my_replication) to be used for the purpose and couldn't figure out which
privileges are necessary. Initially I got an error like "ERROR:  permission
denied for database mydb" . So I used GRANT CREATE ON DATABASE..and that
error disappeared. I have also executed...
ALTER USER my_replication WITH replication;
GRANT usage ON SCHEMA public TO my_replication;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_replication;
When I try
 CREATE PUBLICATION my_publication for table public.term;
I am getting an error
ERROR:  must be owner of table term

What privileges should be granted so that I can do
 CREATE PUBLICATION my_publication FOR TABLES IN SCHEMA public;

Regards,
Jayadevan

Parameter - shared_memory_size

[Jayadevan M]

Hello team,

I am trying to understand the parameter shared_memory_size. It is a static
parameter that reports the size of the shared memory area. Does that imply
that the value may change depending on the memory used by the server?

Regards,
Jayadevan

Re: Doubt on pg_timezone_names and pg_timezone_abbrevs

[Jayadevan M]

On Tue, Apr 1, 2025 at 9:28 AM David G. Johnston 
wrote:

> On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M 
> wrote:
>
>> Hello PG members,
>> I used 'IST'  in a query like this - * (timestamp_hour) at time zone
>> 'IST' time_ist *and did not get the expected output - timestamp in
>> Indian Standard Time. So I queried the 2 views that provide timezone info
>> and did not really understand the abbrev column.
>> select name, abbrev, utc_offset  from pg_timezone_names  where abbrev =
>> 'IST'  ;
>>
>
> Since the S and T are non-location specific you get 26 different timezone
> abbreviations to choose from. That wasn't enough for the world.  So IST is
> non-unique; and for historical reasons Ireland (Eire, which contains
> Dublin) is given default priority.
>
>
>>  name  | abbrev | utc_offset
>> ---++
>>  Eire  | IST| 01:00:00
>>  Asia/Kolkata  | IST| 05:30:00
>>  Asia/Calcutta | IST| 05:30:00
>>  Europe/Dublin | IST| 01:00:00
>>
>
> Suggest you adapt to using ISO names (the name column above) for
> timezones; which are long enough and location-specific enough to be
> unique.  In your case, pick your preferred spelling of Calcutta I suppose.
>
> Thank you. I used Calcutta.
Regards,
Jayadevan

Doubt on pg_timezone_names and pg_timezone_abbrevs

[Jayadevan M]

Hello PG members,
I used 'IST'  in a query like this - * (timestamp_hour) at time zone 'IST'
time_ist *and did not get the expected output - timestamp in Indian
Standard Time. So I queried the 2 views that provide timezone info and did
not really understand the abbrev column.
select name, abbrev, utc_offset  from pg_timezone_names  where abbrev =
'IST'  ;
 name  | abbrev | utc_offset
---++
 Eire  | IST| 01:00:00
 Asia/Kolkata  | IST| 05:30:00
 Asia/Calcutta | IST| 05:30:00
 Europe/Dublin | IST| 01:00:00


select * from pg_timezone_abbrevs where abbrev = 'IST'  ;
 abbrev | utc_offset | is_dst
++
 IST| 02:00:00   | f

This is PostgreSQL 13.15 on AWS RDS. We have the same abbrev for 4
timezones in pg_timezone_names.
Regards,
Jayadevan