Re: get user info on log

[Ganesh Korde]

Hi,

  You can use log_line_prefix in postgresql.conf to log the user name into
the logs.

Regards,
Ganesh Korde.

On Fri, 16 Sep 2022, 6:31 pm Marcos Pegoraro,  wrote:

> Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver <
> adrian.kla...@aklaver.com> escreveu:
>
>> test(5432)=# set role maura;
>> ERROR:  role "maura" does not exist
>> test(5432)=# SET SESSION AUTHORIZATION 'maura';
>> ERROR:  role "maura" does not exist
>>
>> No, I was asking about an error occurring later, not on set session
> authorization command.
>
> set role maura;
> --user Maura exists and set was done correctly
> --now I´m working as Maura
> select 1/0; -- I would like to see this exception on log being logged as
> maura
>
> thanks
> Marcos
>
>

Re: pg_receivewal/xlog to ship wal to cloud

[Ganesh Korde]

On Tue, 19 Jul 2022, 2:09 am Alan Hodgson,  wrote:

> On Mon, 2022-07-18 at 15:55 -0400, neslişah demirci wrote:
> > Hi all,
> >
> > Wondering if anyone has any experience of using pg_receivewal/xlog
> > to ship wal files to GCP/S3?
> >
>
> I use archive_command to send WAL to S3. It works fine. I do gzip
> them before uploading, as they are usually pretty compressible.
>
> I use a lifecycle rule on the bucket to automatically delete ones
> older than I might need.
>
> It's not the fastest process in the world, and replay on our
> reporting server does occasionally fall behind, but I think that's
> true of WAL replay in general as a serialized process.
>

Have a look at this also.

https://supabase.com/blog/2020/08/02/continuous-postgresql-backup-walg

Re: Who am I? Where am I connected?

[Ganesh Korde]

 \conninfo will show you desired details

Regards,
Ganesh Korde.

On Wed, 18 May 2022, 3:38 pm Dominique Devienne, 
wrote:

> LibPQ has various defaults for the host, user, and DB name.
> There's also the password file, the service file and service name.
> In the example below, I can connect with a "naked" psql invocation.
>
> Once connected, can I find out all aspects of the connection string?
> Or where they came from, like a pgpass.conf or service file?
>
> How to get the host, port, db name once connected?
> SHOW and pg_settings does not appear to be it, at first glance.
>
> Thanks, --DD
>
> c:\Users\ddevienne>psql
> psql (12.1, server 14.2)
> WARNING: psql major version 12, server major version 14.
>  Some psql features might not work.
> WARNING: Console code page (437) differs from Windows code page (1252)
>  8-bit characters might not work correctly. See psql reference
>  page "Notes for Windows users" for details.
> Type "help" for help.
>
> ddevienne=>
>
>
>

Re: Cannot find hstore operator

[Ganesh Korde]

On Mon, 24 Jan 2022, 3:22 pm Dominique Devienne, 
wrote:

> Hi. In
> https://www.mail-archive.com/pgsql-general@lists.postgresql.org/msg29321.html
> I asked:
>
> > On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
> > > On Tuesday, January 11, 2022, Dominique Devienne >
> wrote:
> > >> This means the template-schema name is part of the DDL for the schema,
> > >> and a clone would need to use its own search-path, not the original.
> > > This is your major limitation.  You are required to create new objects
> > > from code and cannot leverage any kind of copy of existing objects.
> >
> > But how to avoid that limitation?
> >
> > Triggers in a schema should functions correctly, whether or not client
> > sessions set the search_path, or use fully qualified object names.
> > I was actually surprised that functions from the schema itself (where the
> > trigger is defined), do "not bind more tightly" to the dot (.) schema,
> > the "owner" schema of the trigger, compared to functions elsewhere.
> >
> > Perhaps there's something I'm missing around trigger and name resolution?
>
> But didn't any answer at the time.
>
> But Tom's answer to Paul's question seems to be related to my original
> question, no?
>
> On Sun, Jan 23, 2022 at 4:22 PM Tom Lane  wrote:
> > Paul van der Linden  writes:
> > > Thanks for the clarification, but giving up performance is a no-go for
> us.
> > > Also I have my concerns about shemaqualifying each and every use of
> the ->
> > > operator, there are really a lot of them in my functions and it would
> > > severely impact readability. Are these the only 2 solutions possible?
> >
> > As of v14 you could use SQL-style function definitions, so that the
> > operator is parsed at function definition time instead of runtime.
>
> After re-reading
> https://www.postgresql.org/docs/14/sql-createfunction.html in light of
> Tom's answer,
> does that mean that our `SET search_path TO {0}, 'pg_temp'`
> workaround, in the trigger below,
> to not depend on the search_path at runtime:
> ```
> CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
>  RETURNS trigger
>  LANGUAGE plpgsql
>  SET search_path TO owner_schema_of_fn_tg_tab, 'pg_temp'
> AS $function$
> BEGIN
>  UPDATE AttributeInput
>  SET AppCreateDate = NEW.CreateDate
>  WHERE Guid = NEW.Guid;
>  RETURN NEW;
> END;
> $function$
> ```
> can be re-written as below?
> ```
> CREATE OR REPLACE FUNCTION attributeinput_insert_app_time_ext_tf()
>  RETURNS trigger
>  LANGUAGE sql
> BEGIN ATOMIC
>  UPDATE AttributeInput
>  SET AppCreateDate = NEW.CreateDate
>  WHERE Guid = NEW.Guid;
>  RETURN NEW;
> END;
> ```
> As long as owner_schema_of_fn_tg_tab is first in the search_path at
> function-creation time?
> Or does the v14-specific trick Tom mentioned is not available to
> trigger-returning functions?
> I'm kinda afraid that RETUR NEW; is specific to plpgsql...
>
> I'm still on v12, so cannot test v14 yet. We planned to move to v14,
> for lz4 and built-in uuid-creation function,
> but if we could get rid of the `SET search_path` workaround in our
> trigger functions, that would be even more motivation.
>
> I'd really like my DDL statements to NOT contain schema-specific names,
> to ensure proper name resolution independent of the search_path and
> completely "self-contained" in the schema itself.
> Is there any way to achieve that, beside our current `SET search_path`
> workaround?
>
> Thanks, --DD
>

This might help.

Alter user  SET search_path TO myschema,public;

No need to set search_path every time.

Regards,
Ganesh Korde.

Re: ERROR: ImportError: No module named 'psutil'

[Ganesh Korde]

Ok, thanks guys. Let me check that.

On Sun, Aug 1, 2021 at 10:05 PM Tom Lane  wrote:

> Adrian Klaver  writes:
> > In that vein the OP could run:
>
> > DO $$
> >  import sys
> >  plpy.notice(sys.version)
> > $$ LANGUAGE plpython3u;
>
> > in the database to see what plpython3u is actually pointing at?
>
> +1 ... looking at sys.path in the same way would also be useful.
>
> regards, tom lane
>

ERROR: ImportError: No module named 'psutil'

[Ganesh Korde]

Hi Everyone,

Environment
OS : Linux ubuntu 4.4.0-87-generic
Database:  PostgreSQL 10.7 on x86_64-pc-linux-gnu,
Extension: plpython3u
Python version: Python 3.5.2

 I am trying to run a python function using the language plpython3u and I
am getting the below error, though I have installed psutil.

postgres=# select * from get_psutil_mem();
ERROR:  ImportError: No module named 'psutil'
CONTEXT:  Traceback (most recent call last):
  PL/Python function "get_psutil_mem", line 2, in 
from psutil import virtual_memory, swap_memory
PL/Python function "get_psutil_mem"

 Below is the successful execution of psutil

root@ubuntu:~# python
Python 3.5.2 (default, Jan 26 2021, 13:30:48)
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psutil
>>> psutil.cpu_times()
scputimes(user=139677.25, nice=755.4, system=53408.11, idle=10956992.84,
iowait=18110.06, irq=0.0, softirq=1294.34, steal=0.0, guest=0.0,
guest_nice=0.0)
>>>

Function details:

CREATE OR REPLACE FUNCTION get_psutil_mem(
OUT total float8, OUT used float8, OUT free float8, OUT buff_cache float8,
OUT available float8, OUT percent float8,
OUT swap_total float8, OUT swap_used float8, OUT swap_free float8, OUT
swap_percent float8
)
 LANGUAGE plpython3u
AS $FUNCTION$
from psutil import virtual_memory, swap_memory
vm = virtual_memory()
sw = swap_memory()
return vm.total, vm.used, vm.free, vm.buffers + vm.cached, vm.available,
vm.percent, sw.total, sw.used, sw.free, sw.percent
$FUNCTION$;

The above function is used by pgwatch2 to monitor memory.

*I tried installing python3.4.3 from source code but still the same
error.* Also
tried most of the solutions provided on the internet but nothing helped.
Problem is with the psutil module only.

Please let me know if I am missing anything.

Any help will be much appreciated.

Regards,
Ganesh Korde.

Re: pg_restore (fromuser -> touser)

[Ganesh Korde]

You just need to export dump without any privileges. And while restoring
dump use the new role.

Regards,
Ganesh Korde.

On Mon, 26 Jul 2021, 1:01 am David G. Johnston, 
wrote:

> On Sun, Jul 25, 2021 at 8:39 AM Mayan  wrote:
>
>> Thanks for your reply. Is this something that I can request as a feature
>> add? I don't think it should be too much of effort (based on my limited
>> source code knowledge), but I'm not familiar with the process to request a
>> feature.
>>
>
> You just did.  But unless you are going to fund or help with actual
> development I don't see this going very far.  This has been inquired about
> many times in the past yet the feature still does not exist.
>
> David J.
>
>

Re: Doubt on pgbouncer

[Ganesh Korde]

Try using HAProxy.

Regards,
Ganesh Korde.

On Sat, 3 Jul 2021, 9:06 pm Rama Krishnan,  wrote:

>
> Hi Team,
>
> How can I split read and write queries using pgbouncer
>
> Regards
>
> A.Rama Krishnan
>
>
> <https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
>  Virus-free.
> www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail>
> <#m_3619964688060668000_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

Re: Any insights on Qlik Sense using CURSOR ?

[Ganesh Korde]

Hi,
On Tue, May 18, 2021 at 6:22 PM Franck Routier (perso) 
wrote:

> Hi,
>
> I am using postgresql 12.7 on Ubunut as a datawarehouse, that is then
> queried by QlikSense to produce business analytics.
>
> One of my dataloaders, that runs multiple queries, sometimes takes about
> 3 hours to feed Qlik with the relevant records (about 10M records), but
> sometimes goes crazy and times out (as Qlik stops it when it takes more
> than 480 minutes).
>
> The point is that Qlik is using a CURSOR to retrive the data. I'm not
> familiar with CURSOR and postgresql documentation mainly cites functions
> as use case. I don't really know how Qlik creates these cursors when
> executing my queries...
>
> I tried load_min_duration to pinpoint the problem, but only shows things
> like that:
>
> ...
> LOG: duration : 294774.600 ms, instruction : fetch 10 in "SQL_CUR4"
> LOG: duration : 282867.279 ms, instruction : fetch 10 in "SQL_CUR4"
> ...
>
> So I don't know exactly which of my queries is hiding behind
> "SQL_CUR4"...
>
> Is there a way to log the actual query ?
> Is using a CURSOR a best practice to retrieve big datasets ? (it seems
> Qlik is using it for every connection on Postgresql)
> Does each FETCH re-run the query, or is the result somehow cached (on
> disk ?) ?
>
> Thanks for any insight on CURSOR and/or Qlik queries on Postgresql !
>
> Best regards,
> Franck
>
> Have you tried setting the parameter below?
log_statement = 'all'

you will get all queries logged into log files.

Regards,
Ganesh Korde.

Re: Who altered the database?

[Ganesh Korde]

On Fri, 9 Apr 2021, 4:44 pm Durumdara,  wrote:

> Dear Members!
>
> We have a server.
> Because of high usage we can't log DDL-s. There are too many temporary
> tables created by users, so it would slow down the server.
> A database owner changed.
>
> What can we do to get info about the change (who did it, from what IP,
> when it happened) in the future?
>
> As I see we can't define precisely what we want to log... All or nothing.
>
> Do you know any tricks to do it?
>
> For example: DB level trigger, or other thing?
>
> Thank you for the answer!
>
> Best regards
>
> dd
>
>
>
You can use different options in log_line_prefix parameter in
postgresql.conf
You can also log connection and disconnection, each has separate parameter
in configuration file.

Regards,
Ganesh Korde.

>

Re: Streaming replication between different OS

[Ganesh Korde]

Should not be a problem if both OS archtecture (32 bit/ 64 bit) are same.

On Mon, 22 Feb 2021, 11:48 am Atul Kumar,  wrote:

> Hi,
>
>
> I have postgres 9.6 cluster running on Centos 6.8, so I just wanted to
> know that can I configure streaming replication with same postgres version
> i.e 9.6 running on centos 7.
>
> Suggestions are welcome as the Centos versions are different one is 6.8
> and second one is 7.
>
> Also please let me know if there will be any challenge in case of failover.
>

Re: Insertion time is very high for inserting data in postgres

[Ganesh Korde]

On Wed, 10 Feb 2021, 1:56 pm prachi surangalikar, <
surangalikarprachi...@gmail.com> wrote:

> Hello Team,
> Greetings!
>
> We are using Postgres 12.2.1 for fetching per minute data for about 25
> machines but running parallely via a single thread in python.
> But suddenly the insertion time has increased to a very high level, about
> 30 second for one machine.
> We are in so much problem as the data fetching is becoming slow.
>
> if anyone could help us to solve this problem it would be of great help to
> us.
>
Are you doing vaccuum analyze table regularly?  If not then that might
delay insertion.

Re: PostgreSQL HA

[Ganesh Korde]

You can go for BDR from 2nd Quadrant.

On Sat, 26 Dec 2020, 4:17 pm venkata786 k,  wrote:

> Hi Team,
>
> Could you please share postgresql's best active(R/W)-active(R/W) (multi
> master replication) solutions.
> My Team is thinking about implementing active-active replacing
> master-slave.
>
> Regards,
> Venkat
>
>
>
>
>
>
> 
>  Virus-free.
> www.avast.com
> 
> <#m_-8285752177786612489_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

Re: psql asks for password despite configuring trust authentication

[Ganesh Korde]

Please make sure you are not using -w option while connecting through psql.

On Thu, 29 Oct 2020, 11:30 pm Atul Kumar,  wrote:

> hi,
>
> My problem is, that I am always asked for password when trying to
> interact with the database or access it, although the authentication
> is set to trust for all users and databases.
>
> Please help.
>
>
>
> Regards,
> Atul
>
>
>

Re: UPDATE on 20 Million Records Transaction or not?

[Ganesh Korde]

You can try this

First take backup of table then execute below statements.

create table members_temp
as
select ,'2038-01-18'  regdate from  members where regdate =
'2020-07-07';

delete from  members  where regdate = '2020-07-07';

insert into  members  select * from  members_temp ;

drop table  members_temp;

Regards,
Ganesh Korde.

On Tue, Jun 23, 2020 at 9:06 PM Ron  wrote:

>
> I'd make a copy of the table, and test how long the various methods take.
>
> On 6/23/20 10:17 AM, Jason Ralph wrote:
>
>
>
> Hello List,
>
> PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (R
>
> ed Hat 4.4.7-23), 64-bit
>
>
>
> I am planning an update on a table with 20Million records, I have been
> researching the best practices.  I will remove all indexes and foreign keys
> prior to the update, however I am not sure if I should use a transaction or
> not.
>
> My thought process is that a transaction would be easier to recover if
> something fails, however it would take more time to write to the WAL log in
> a transaction.
>
>
> >Are you updating *every* row in the table?
>
> No I am using an update like so: UPDATE members SET regdate='2038-01-18'
> WHERE regdate='2020-07-07'
>
> DB=# select count(*) from members where regdate = '2020-07-07';
>
>   count
>
> --
>
> 17333090
>
> (1 row)
>
> >Are you updating indexed fields?  (If not, then leave the indexes and
> FKs, since they won't be touched.)
>
> Just checked regdate is not indexed so I will leave them in place.
>
>
>
>
> Would it make sense to make a back up of the table then execute update
> without a transaction?
>
>
> >Always make a backup.
>
> Agreed
>
>
> How would you guys do it?
>
>
> >It depends on what percentage of the rows are being updated, which
> columns are being updated and how big the records are.
>
> Please see above, thanks
>
>
>
> *Jason Ralph*
>
>
>
> *From:* Ron  
> *Sent:* Tuesday, June 23, 2020 10:57 AM
> *To:* pgsql-general@lists.postgresql.org
> *Subject:* Re: UPDATE on 20 Million Records Transaction or not?
>
>
>
> On 6/23/20 8:32 AM, Jason Ralph wrote:
>
> Hello List,
>
> PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (R
>
> ed Hat 4.4.7-23), 64-bit
>
>
>
> I am planning an update on a table with 20Million records, I have been
> researching the best practices.  I will remove all indexes and foreign keys
> prior to the update, however I am not sure if I should use a transaction or
> not.
>
> My thought process is that a transaction would be easier to recover if
> something fails, however it would take more time to write to the WAL log in
> a transaction.
>
>
> Are you updating *every* row in the table?
>
> Are you updating indexed fields?  (If not, then leave the indexes and FKs,
> since they won't be touched.)
>
>
>
>
> Would it make sense to make a back up of the table then execute update
> without a transaction?
>
>
> Always make a backup.
>
>
> How would you guys do it?
>
>
> It depends on what percentage of the rows are being updated, which columns
> are being updated and how big the records are.
>
> --
> Angular momentum makes the world go 'round.
> This message contains confidential information and is intended only for
> the individual named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately by e-mail if you have received this e-mail by mistake and
> delete this e-mail from your system. E-mail transmission cannot be
> guaranteed to be secure or error-free as information could be intercepted,
> corrupted, lost, destroyed, arrive late or incomplete, or contain viruses.
> The sender therefore does not accept liability for any errors or omissions
> in the contents of this message, which arise as a result of e-mail
> transmission. If verification is required please request a hard-copy
> version.
>
>
> --
> Angular momentum makes the world go 'round.
>

Re: Extract transactions from wals ??

[Ganesh Korde]

I think he wants to see data from different tables at different timestamp
(like flashback query in Oracle). As per my understanding question here is
can PITR be done for specific table and for specific timestamp.

On Fri, Nov 22, 2019 at 2:37 PM Laurenz Albe 
wrote:

> On Thu, 2019-11-21 at 17:35 +0100, Marc Millas wrote:
> > I was writing select from ""table"" as a template. We have to do this
> for a bunch of tables.
> > So, to my understanding, what you suggest is to PITR up to the first
> timestamp,
> > extract all meaningfull tables, and then pitr to the second timestamp
> > so as to be able to script a kind of "diff" between the 2 to get what I
> want.
>
> Sure, you can do that.
>
> The description of what you wanted to do was rather unclear, all I could
> make out is that you want to query AS OF TIMESTAMP.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>