Re: HASH partitioning not working properly

[Laurenz Albe]

On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
> On Fri, Jun 19, 2020 at 11:44 AM David Rowley  wrote:
> > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N  wrote:
> > >After seeing the below, I feel partitioning is not working properly or 
> > > it maybe case that my understanding is wrong.  Can somebody explain me 
> > > what is happening?
> > 
> > It's your understanding that's not correct.  The value of is passed
> > through a hash function and the partition is selected based partition
> > matching the remainder value after dividing the return value of the
> > hash function by the largest modulus of any partition.
> > 
> > That might surprise you, but how would you select which partition a
> > varchar value should go into if you didn't use a hash function.
> > 
> > David
> 
> How can I see the output of hash function that is used internally?

In the case of "integer", the hash function is "pg_catalog"."hashint4".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: A query in Streaming Replication

[Kyotaro Horiguchi]

At Sat, 13 Jun 2020 23:12:25 +0530, Sreerama Manoj 
 wrote in 
> Hello,
> 
> Forgot to add the version details. I'm using postgres 10.7 .
> 
> On Sat, Jun 13, 2020, 20:26 Sreerama Manoj 
> wrote:
> 
> > Hello,
> >   I use streaming replication in async mode. When master gets down,
> > slave will be promoted using a trigger file. During this process ".partial"
> > file will be created and a WAL file with same ID will be created in a
> > different time line in slave. When master comes back as slave, it will be
> > synced to the current master. This is the normal procedure as far as I
> > understood. But in some cases, ".partial" file is not getting created and

It is not guaranteed that a crashed master can be used as a new
standby as-is, since there can be unsent WAL records on the old master
after the LSN where the new master promoted.  If files are assumed to
be sound, pg_rewind will adjust the old master as  a new standby.

The .partial file is created when timeline diverges at midst of a WAL
segment. It is useful when performing PITR to the end of the same
timeline, rather than going into the next timeline.  I don't have an
idea at hand of how this can be relevant to the reusability of the old
master..

> > peer DB which comes back as slave is unable to sync when this happens..
> > Please suggest if this happens in any scenario and how to overcome this.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: HASH partitioning not working properly

[Srinivasa T N]

On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe 
wrote:

> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley 
> wrote:
> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N  wrote:
> > > >After seeing the below, I feel partitioning is not working
> properly or it maybe case that my understanding is wrong.  Can somebody
> explain me what is happening?
> > >
> > > It's your understanding that's not correct.  The value of is passed
> > > through a hash function and the partition is selected based partition
> > > matching the remainder value after dividing the return value of the
> > > hash function by the largest modulus of any partition.
> > >
> > > That might surprise you, but how would you select which partition a
> > > varchar value should go into if you didn't use a hash function.
> > >
> > > David
> >
> > How can I see the output of hash function that is used internally?
>
> In the case of "integer", the hash function is "pg_catalog"."hashint4".
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
> I guess output formatting is wrong, any help?

 postgres=# select pg_catalog.hashint4(7);
  hashint4

 -978793473
(1 row)

Regards,
Seenu.

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi,

Thank you all for help. 

> FWIW in case you haven't tried yet, if you could find a DETAILS: line
> following to the ERROR: canceling.." message in server log, it would
> narrow the possibility.


I executed a database dump with 4 jobs and here are logs how this ended.

2020-06-19 02:38:28 UTC:[30953]:ERROR:40001:canceling statement due to conflict 
with recovery
2020-06-19 02:38:28 UTC:[30953]:DETAIL:User query might have needed to see row 
versions that must be removed.
2020-06-19 02:38:28 UTC:[30953]:LOCATION:ProcessInterrupts, postgres.c:3057
2020-06-19 02:38:28 UTC:[30953]:STATEMENT:
2020-06-19 02:38:28 UTC:[30926]:FATAL:40001:terminating connection due to 
conflict with recovery
2020-06-19 02:38:28 UTC:[30926]:DETAIL:User query might have needed to see row 
versions that must be removed.
2020-06-19 02:38:28 UTC:[30926]:HINT:In a moment you should be able to 
reconnect to the database and repeat your command.
2020-06-19 02:38:28 UTC:[30926]:LOCATION:ProcessInterrupts, postgres.c:2987
2020-06-19 02:38:28 UTC:[30952]:ERROR:40001:canceling statement due to conflict 
with recovery
2020-06-19 02:38:28 UTC:[30952]:DETAIL:User query might have needed to see row 
versions that must be removed.
2020-06-19 02:38:28 UTC:[30952]:LOCATION:ProcessInterrupts, postgres.c:3057
2020-06-19 02:38:28 UTC:[30952]:STATEMENT:
2020-06-19 02:38:28 UTC:[30953]:LOG:08006:could not receive data from 
client:Connection reset by peer
2020-06-19 02:38:28 UTC:[30953]:LOCATION:pq_recvbuf, pqcomm.c:978

Does it give any hints for anyone? Or how to find right version of source code 
what to analyse?

BR,
Toomas

Re: HASH partitioning not working properly

[Amul Sul]

On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N  wrote:
>
> On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe  
> wrote:
>>
>> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
>> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley  wrote:
>> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N  wrote:
>> > > >After seeing the below, I feel partitioning is not working properly 
>> > > > or it maybe case that my understanding is wrong.  Can somebody explain 
>> > > > me what is happening?
>> > >
>> > > It's your understanding that's not correct.  The value of is passed
>> > > through a hash function and the partition is selected based partition
>> > > matching the remainder value after dividing the return value of the
>> > > hash function by the largest modulus of any partition.
>> > >
>> > > That might surprise you, but how would you select which partition a
>> > > varchar value should go into if you didn't use a hash function.
>> > >
>> > > David
>> >
>> > How can I see the output of hash function that is used internally?
>>
>> In the case of "integer", the hash function is "pg_catalog"."hashint4".
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
> I guess output formatting is wrong, any help?
>
>  postgres=# select pg_catalog.hashint4(7);
>   hashint4
> 
>  -978793473
> (1 row)
>
Instead of direct hash function, the easiest way to use
satisfies_hash_partition() what is used in defining hash
partitioning constraint.

You can see the partition constraint by description partition table i.e.
 use \d+ busbar_version5.

Regards,
Amul

Re: HASH partitioning not working properly

[Srinivasa T N]

On Fri, Jun 19, 2020 at 3:09 PM Amul Sul  wrote:

> On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N  wrote:
> >
> > On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe 
> wrote:
> >>
> >> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
> >> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley 
> wrote:
> >> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N 
> wrote:
> >> > > >After seeing the below, I feel partitioning is not working
> properly or it maybe case that my understanding is wrong.  Can somebody
> explain me what is happening?
> >> > >
> >> > > It's your understanding that's not correct.  The value of is passed
> >> > > through a hash function and the partition is selected based
> partition
> >> > > matching the remainder value after dividing the return value of the
> >> > > hash function by the largest modulus of any partition.
> >> > >
> >> > > That might surprise you, but how would you select which partition a
> >> > > varchar value should go into if you didn't use a hash function.
> >> > >
> >> > > David
> >> >
> >> > How can I see the output of hash function that is used internally?
> >>
> >> In the case of "integer", the hash function is "pg_catalog"."hashint4".
> >>
> >> Yours,
> >> Laurenz Albe
> >> --
> >> Cybertec | https://www.cybertec-postgresql.com
> >>
> > I guess output formatting is wrong, any help?
> >
> >  postgres=# select pg_catalog.hashint4(7);
> >   hashint4
> > 
> >  -978793473
> > (1 row)
> >
> Instead of direct hash function, the easiest way to use
> satisfies_hash_partition() what is used in defining hash
> partitioning constraint.
>
> You can see the partition constraint by description partition table i.e.
>  use \d+ busbar_version5.
>
> Regards,
> Amul
>

Sorry, I did not get you.

My current \d+ is

postgres=# \d+ busbar_version6;
Table "test.busbar_version6"
  Column  |  Type   | Collation | Nullable | Default | Storage | Stats
target |
Description
--+-+---+--+-+-+--+-

 objectid | integer |   |  | | plain   |
   |
 ver_id   | integer |   |  | | plain   |
   |
Partition of: busbar_version FOR VALUES WITH (modulus 10, remainder 6)
Partition constraint: satisfies_hash_partition('16397'::oid, 10, 6, ver_id)
Access method: heap

Regards,
Seenu.

Re: create batch script to import into postgres tables

[Pepe TD Vo]

thank you,   I tried that too, remove the quote around the echo and it prompt 
for password, as I mentioned no matter I put -P mypassword no matter what I 
spell out password=mypassword still argument error
>>echo select count(*) from tableA; | "C:\Program Files\PostgreSQL\11\bin\psql" 
>>-U PSmasteruser -d PSCIDR -h hostname.amazonaws.com -p 5432  

>> echo select count(*) from tableA; | "C:\Program 
>>Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
>>hostname.amazonaws.com -p 5432 password=mypassword
all usernames are same password.
thank you so much for all input.
v/r,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Thursday, June 18, 2020, 09:25:41 PM EDT,  wrote:  
 
 
Remove the quotes around echo

echo select count(*) from web_20200619; | "C:\Program 
Files\postgresql\11\bin\psql" -d *** -h *** -U ***

or, store your query into a text file and use 

psql -f query.sql




Sent from my mobile phone

Le 19 juin 2020 à 02:00, Adrian Klaver  a écrit :



On 6/18/20 4:37 PM, Pepe TD Vo wrote:

thank you for the link.  I did try it and it's still error


echo 'SELECT count(*) FROM tableA;' |


C:\Program Files\PostgreSQL\11\bin\psql -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432



This is getting old. The error is?

I can run 'psql -u postgres -d PSCIDR -p 5432' (on Linux server, still learning 
how to run it from psql shell in Window) fine from psql prompt.  Just still 
wonder how to connect directly to the instance PSCIDR from scripting in both 
aws and linux.  Otherwise manually run using pgAdmin.


v/r,


**


*Bach-Nga



-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: HASH partitioning not working properly

[Amul Sul]

On Fri, Jun 19, 2020 at 3:50 PM Srinivasa T N  wrote:
>
>
>
> On Fri, Jun 19, 2020 at 3:09 PM Amul Sul  wrote:
>>
>> On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N  wrote:
>> >
>> > On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe  
>> > wrote:
>> >>
>> >> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
>> >> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley  
>> >> > wrote:
>> >> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N  wrote:
>> >> > > >After seeing the below, I feel partitioning is not working 
>> >> > > > properly or it maybe case that my understanding is wrong.  Can 
>> >> > > > somebody explain me what is happening?
>> >> > >
>> >> > > It's your understanding that's not correct.  The value of is passed
>> >> > > through a hash function and the partition is selected based partition
>> >> > > matching the remainder value after dividing the return value of the
>> >> > > hash function by the largest modulus of any partition.
>> >> > >
>> >> > > That might surprise you, but how would you select which partition a
>> >> > > varchar value should go into if you didn't use a hash function.
>> >> > >
>> >> > > David
>> >> >
>> >> > How can I see the output of hash function that is used internally?
>> >>
>> >> In the case of "integer", the hash function is "pg_catalog"."hashint4".
>> >>
>> >> Yours,
>> >> Laurenz Albe
>> >> --
>> >> Cybertec | https://www.cybertec-postgresql.com
>> >>
>> > I guess output formatting is wrong, any help?
>> >
>> >  postgres=# select pg_catalog.hashint4(7);
>> >   hashint4
>> > 
>> >  -978793473
>> > (1 row)
>> >
>> Instead of direct hash function, the easiest way to use
>> satisfies_hash_partition() what is used in defining hash
>> partitioning constraint.
>>
>> You can see the partition constraint by description partition table i.e.
>>  use \d+ busbar_version5.
>>
>> Regards,
>> Amul
>
>
> Sorry, I did not get you.
>
> My current \d+ is
>
> postgres=# \d+ busbar_version6;
> Table "test.busbar_version6"
>   Column  |  Type   | Collation | Nullable | Default | Storage | Stats target 
> |
> Description
> --+-+---+--+-+-+--+-
> 
>  objectid | integer |   |  | | plain   |  
> |
>  ver_id   | integer |   |  | | plain   |  
> |
> Partition of: busbar_version FOR VALUES WITH (modulus 10, remainder 6)
> Partition constraint: satisfies_hash_partition('16397'::oid, 10, 6, ver_id)
> Access method: heap
>
By executing "SELECT satisfies_hash_partition('16397'::oid, 10, 6, ) "
will tell you whether  fits in the partition having modulus 10 and
remainder 6 or not.

Regards,
Amul

Re: HASH partitioning not working properly

[Srinivasa T N]

On Fri, Jun 19, 2020, 5:45 PM Amul Sul  wrote:

> On Fri, Jun 19, 2020 at 3:50 PM Srinivasa T N  wrote:
> >
> >
> >
> > On Fri, Jun 19, 2020 at 3:09 PM Amul Sul  wrote:
> >>
> >> On Fri, Jun 19, 2020 at 1:28 PM Srinivasa T N 
> wrote:
> >> >
> >> > On Fri, Jun 19, 2020 at 12:34 PM Laurenz Albe <
> laurenz.a...@cybertec.at> wrote:
> >> >>
> >> >> On Fri, 2020-06-19 at 12:12 +0530, Srinivasa T N wrote:
> >> >> > On Fri, Jun 19, 2020 at 11:44 AM David Rowley <
> dgrowle...@gmail.com> wrote:
> >> >> > > On Fri, 19 Jun 2020 at 17:42, Srinivasa T N 
> wrote:
> >> >> > > >After seeing the below, I feel partitioning is not working
> properly or it maybe case that my understanding is wrong.  Can somebody
> explain me what is happening?
> >> >> > >
> >> >> > > It's your understanding that's not correct.  The value of is
> passed
> >> >> > > through a hash function and the partition is selected based
> partition
> >> >> > > matching the remainder value after dividing the return value of
> the
> >> >> > > hash function by the largest modulus of any partition.
> >> >> > >
> >> >> > > That might surprise you, but how would you select which
> partition a
> >> >> > > varchar value should go into if you didn't use a hash function.
> >> >> > >
> >> >> > > David
> >> >> >
> >> >> > How can I see the output of hash function that is used internally?
> >> >>
> >> >> In the case of "integer", the hash function is
> "pg_catalog"."hashint4".
> >> >>
> >> >> Yours,
> >> >> Laurenz Albe
> >> >> --
> >> >> Cybertec | https://www.cybertec-postgresql.com
> >> >>
> >> > I guess output formatting is wrong, any help?
> >> >
> >> >  postgres=# select pg_catalog.hashint4(7);
> >> >   hashint4
> >> > 
> >> >  -978793473
> >> > (1 row)
> >> >
> >> Instead of direct hash function, the easiest way to use
> >> satisfies_hash_partition() what is used in defining hash
> >> partitioning constraint.
> >>
> >> You can see the partition constraint by description partition table i.e.
> >>  use \d+ busbar_version5.
> >>
> >> Regards,
> >> Amul
> >
> >
> > Sorry, I did not get you.
> >
> > My current \d+ is
> >
> > postgres=# \d+ busbar_version6;
> > Table "test.busbar_version6"
> >   Column  |  Type   | Collation | Nullable | Default | Storage | Stats
> target |
> > Description
> >
> --+-+---+--+-+-+--+-
> > 
> >  objectid | integer |   |  | | plain   |
>   |
> >  ver_id   | integer |   |  | | plain   |
>   |
> > Partition of: busbar_version FOR VALUES WITH (modulus 10, remainder 6)
> > Partition constraint: satisfies_hash_partition('16397'::oid, 10, 6,
> ver_id)
> > Access method: heap
> >
> By executing "SELECT satisfies_hash_partition('16397'::oid, 10, 6,
> ) "
> will tell you whether  fits in the partition having modulus 10 and
> remainder 6 or not.
>
> Regards,
> Amul
>

OK.. Thanks.

BTW, is it possible to have a custom hash function instead of predefined
hash function?

Regards,
Seenu.

>

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 4:12 AM, Pepe TD Vo wrote:
thank you,   I tried that too, remove the quote around the echo and it 
prompt for password, as I mentioned no matter I put -P mypassword no 
matter what I spell out password=mypassword still argument error


Once again -P has nothing to do with password. Also --password does not 
take an argument, it is meant to be used as is. The purpose is to force 
a password prompt. This is all spelled out here:


https://www.postgresql.org/docs/12/app-psql.html

Also spelled out in above is:

" It is also convenient to have a ~/.pgpass file to avoid regularly 
having to type in passwords. See Section 33.15 for more information."


And Section 33.15:

https://www.postgresql.org/docs/12/libpq-pgpass.html

"The file .pgpass in a user's home directory can contain passwords to be 
used if the connection requires a password (and no password has been 
specified otherwise).  ..."


Read more at link for how to do that.



 >>echo select count(*) from tableA; | "C:\Program 
Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432


 >> echo select count(*) from tableA; | "C:\Program 
Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432 password=mypassword


all usernames are same password.

thank you so much for all input.

v/r,

**
*Bach-Nga


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: HASH partitioning not working properly

[Laurenz Albe]

On Fri, 2020-06-19 at 13:27 +0530, Srinivasa T N wrote:
> > > How can I see the output of hash function that is used internally?
> > 
> > In the case of "integer", the hash function is "pg_catalog"."hashint4".
>
> I guess output formatting is wrong, any help?
>
>  postgres=# select pg_catalog.hashint4(7);
>   hashint4  
> 
>  -978793473
> (1 row)

No, that is fine.
Just take the result mod 10 if that is how hash partitioning was defined:

select pg_catalog.hashint4(7) - floor(pg_catalog.hashint4(7) / 10.0) * 10;
 ?column? 
--
7
(1 row)

So that should end up in the eighth partition.

You have no choice which hash function to use for partitioning.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Conflict with recovery on PG version 11.6

[Laurenz Albe]

On Fri, 2020-06-19 at 11:46 +0300, Toomas Kristin wrote:
> I executed a database dump with 4 jobs and here are logs how this ended.
> 
> 2020-06-19 02:38:28 UTC:[30953]:ERROR:40001:canceling statement due to 
> conflict with recovery
> 2020-06-19 02:38:28 UTC:[30953]:DETAIL:User query might have needed to see 
> row versions that must be removed.
> 2020-06-19 02:38:28 UTC:[30953]:LOCATION:ProcessInterrupts, postgres.c:3057
> 2020-06-19 02:38:28 UTC:[30953]:STATEMENT:
> 2020-06-19 02:38:28 UTC:[30926]:FATAL:40001:terminating connection due to 
> conflict with recovery
> 2020-06-19 02:38:28 UTC:[30926]:DETAIL:User query might have needed to see 
> row versions that must be removed.
> 2020-06-19 02:38:28 UTC:[30926]:HINT:In a moment you should be able to 
> reconnect to the database and repeat your command.
> 2020-06-19 02:38:28 UTC:[30926]:LOCATION:ProcessInterrupts, postgres.c:2987
> 2020-06-19 02:38:28 UTC:[30952]:ERROR:40001:canceling statement due to 
> conflict with recovery
> 2020-06-19 02:38:28 UTC:[30952]:DETAIL:User query might have needed to see 
> row versions that must be removed.
> 2020-06-19 02:38:28 UTC:[30952]:LOCATION:ProcessInterrupts, postgres.c:3057
> 2020-06-19 02:38:28 UTC:[30952]:STATEMENT:
> 2020-06-19 02:38:28 UTC:[30953]:LOG:08006:could not receive data from 
> client:Connection reset by peer
> 2020-06-19 02:38:28 UTC:[30953]:LOCATION:pq_recvbuf, pqcomm.c:978
> 
> Does it give any hints for anyone? Or how to find right version of source 
> code what to analyse?

Yes, that are conflicts with VACUUM.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: create batch script to import into postgres tables

[Pepe TD Vo]

Thank you sir and I am sorry for the typo not having "--" on password.  I did 
spelling out with --password=mypassword
>> echo select count(*) from tableA; | "C:\Program 
>>Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
>>hostname.amazonaws.com -p 5432 --password=mypassword

even -W for password>> echo select count(*) from tableA; | "C:\Program 
Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432 -W=mypassword

none of them work, still prompt me for password to type in.  I will look into 
the pgpassfile which I know it will fail again.

very respectfully,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Friday, June 19, 2020, 09:19:35 AM EDT, Adrian Klaver 
 wrote:  
 
 On 6/19/20 4:12 AM, Pepe TD Vo wrote:
> thank you,   I tried that too, remove the quote around the echo and it 
> prompt for password, as I mentioned no matter I put -P mypassword no 
> matter what I spell out password=mypassword still argument error

Once again -P has nothing to do with password. Also --password does not 
take an argument, it is meant to be used as is. The purpose is to force 
a password prompt. This is all spelled out here:

https://www.postgresql.org/docs/12/app-psql.html

Also spelled out in above is:

" It is also convenient to have a ~/.pgpass file to avoid regularly 
having to type in passwords. See Section 33.15 for more information."

And Section 33.15:

https://www.postgresql.org/docs/12/libpq-pgpass.html

"The file .pgpass in a user's home directory can contain passwords to be 
used if the connection requires a password (and no password has been 
specified otherwise).  ..."

Read more at link for how to do that.

> 
>  >>echo select count(*) from tableA; | "C:\Program 
> Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
> hostname.amazonaws.com -p 5432
> 
>  >> echo select count(*) from tableA; | "C:\Program 
> Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
> hostname.amazonaws.com -p 5432 password=mypassword
> 
> all usernames are same password.
> 
> thank you so much for all input.
> 
> v/r,
> 
> **
> *Bach-Nga

-- 
Adrian Klaver
adrian.kla...@aklaver.com


  

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 6:53 AM, Pepe TD Vo wrote:
Thank you sir and I am sorry for the typo not having "--" on password.  
I did spelling out with --password=mypassword


Please go back and read my post again.



echo select count(*) from tableA; | "C:\Program 
Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432 --password=mypassword


even -W for password
echo select count(*) from tableA; | "C:\Program 
Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h 
hostname.amazonaws.com -p 5432 -W=mypassword


none of them work, still prompt me for password to type in.  I will look 
into the pgpassfile which I know it will fail again.



very respectfully,

**
*Bach-Nga

*No one in this world is pure and perfect.  If you avoid people for 
their mistakes you will be alone. So judge less, love, and forgive 
more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he 
had four legs, a tail, and barked, I admit he was, to all outward 
appearances. But to those who knew him well, he was a perfect gentleman 
(Hermione Gingold)


**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, June 19, 2020, 09:19:35 AM EDT, Adrian Klaver 
 wrote:



On 6/19/20 4:12 AM, Pepe TD Vo wrote:
 > thank you,   I tried that too, remove the quote around the echo and it
 > prompt for password, as I mentioned no matter I put -P mypassword no
 > matter what I spell out password=mypassword still argument error

Once again -P has nothing to do with password. Also --password does not
take an argument, it is meant to be used as is. The purpose is to force
a password prompt. This is all spelled out here:

https://www.postgresql.org/docs/12/app-psql.html

Also spelled out in above is:

" It is also convenient to have a ~/.pgpass file to avoid regularly
having to type in passwords. See Section 33.15 for more information."

And Section 33.15:

https://www.postgresql.org/docs/12/libpq-pgpass.html

"The file .pgpass in a user's home directory can contain passwords to be
used if the connection requires a password (and no password has been
specified otherwise).  ..."

Read more at link for how to do that.


 >
 >  >>echo select count(*) from tableA; | "C:\Program
 > Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h
 > hostname.amazonaws.com -p 5432
 >
 >  >> echo select count(*) from tableA; | "C:\Program
 > Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h
 > hostname.amazonaws.com -p 5432 password=mypassword
 >
 > all usernames are same password.
 >
 > thank you so much for all input.
 >
 > v/r,
 >
 > **
 > *Bach-Nga

--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 7:17 AM, pepevo wrote:
I understand your post about "password does not take an argument, it is 
meant to be used as is. The purpose is to force a password prompt." When 
I used -W and --password=.  That's what I said I will try pgpassfile.  
Thought it like mysq/oracle can indicate out without creating password 
file.


If you want to expose your password in the script file then:

https://www.postgresql.org/docs/12/app-psql.html

Usage
Connecting to a Database

"An alternative way to specify connection parameters is in a conninfo 
string or a URI, which is used instead of a database name. This 
mechanism give you very wide control over the connection. For example:


$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require

This way you can also use LDAP for connection parameter lookup as 
described in Section 33.17. See Section 33.1.2 for more information on 
all the available connection options."


So:

psql 
postgresql://PSmasteruser:mypassw...@hostname.amazonaws.com:5432/PSCIDR


or

psql 'dbname=PSCIDR user=PSmasteruser host=hostname.amazonaws.com port= 
5432 password=mypassword '




Thank you again.

Bach-Nga

Sent from my Metro By T-Mobile 4G LTE Android Device


 Original message 
From: Adrian Klaver 
Date: 6/19/20 09:58 (GMT-05:00)
To: Pepe TD Vo , cgerard...@gmail.com
Cc: Christopher Browne , Pgsql-admin 
, Pgsql-general 

Subject: Re: create batch script to import into postgres tables

On 6/19/20 6:53 AM, Pepe TD Vo wrote:
 > Thank you sir and I am sorry for the typo not having "--" on password.
 > I did spelling out with --password=mypassword

Please go back and read my post again.

 >
 >>> echo select count(*) from tableA; | "C:\Program
 > Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h
 > hostname.amazonaws.com -p 5432 --password=mypassword
 >
 > even -W for password
 >>> echo select count(*) from tableA; | "C:\Program
 > Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h
 > hostname.amazonaws.com -p 5432 -W=mypassword
 >
 > none of them work, still prompt me for password to type in.  I will look
 > into the pgpassfile which I know it will fail again.
 >
 >
 > very respectfully,
 >
 > **
 > *Bach-Nga
 >
 > *No one in this world is pure and perfect.  If you avoid people for
 > their mistakes you will be alone. So judge less, love, and forgive
 > more.EmojiEmojiEmoji
 > To call him a dog hardly seems to do him justice though in as much as he
 > had four legs, a tail, and barked, I admit he was, to all outward
 > appearances. But to those who knew him well, he was a perfect gentleman
 > (Hermione Gingold)
 >
 > **Live simply **Love generously **Care deeply **Speak kindly.
 > *** Genuinely rich *** Faithful talent *** Sharing success
 >
 >
 >
 >
 > On Friday, June 19, 2020, 09:19:35 AM EDT, Adrian Klaver
 >  wrote:
 >
 >
 > On 6/19/20 4:12 AM, Pepe TD Vo wrote:
 >  > thank you,   I tried that too, remove the quote around the echo and it
 >  > prompt for password, as I mentioned no matter I put -P mypassword no
 >  > matter what I spell out password=mypassword still argument error
 >
 > Once again -P has nothing to do with password. Also --password does not
 > take an argument, it is meant to be used as is. The purpose is to force
 > a password prompt. This is all spelled out here:
 >
 > https://www.postgresql.org/docs/12/app-psql.html
 >
 > Also spelled out in above is:
 >
 > " It is also convenient to have a ~/.pgpass file to avoid regularly
 > having to type in passwords. See Section 33.15 for more information."
 >
 > And Section 33.15:
 >
 > https://www.postgresql.org/docs/12/libpq-pgpass.html
 >
 > "The file .pgpass in a user's home directory can contain passwords to be
 > used if the connection requires a password (and no password has been
 > specified otherwise).  ..."
 >
 > Read more at link for how to do that.
 >
 >
 >  >
 >  >  >>echo select count(*) from tableA; | "C:\Program
 >  > Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h
 >  > hostname.amazonaws.com -p 5432
 >  >
 >  >  >> echo select count(*) from tableA; | "C:\Program
 >  > Files\PostgreSQL\11\bin\psql" -U PSmasteruser -d PSCIDR -h
 >  > hostname.amazonaws.com -p 5432 password=mypassword
 >  >
 >  > all usernames are same password.
 >  >
 >  > thank you so much for all input.
 >  >
 >  > v/r,
 >  >
 >  > **
 >  > *Bach-Nga
 >
 > --
 > Adrian Klaver
 > adrian.kla...@aklaver.com 
 >
 >


--
Adrian Klaver
adrian.kla...@aklaver.com





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: create batch script to import into postgres tables

[David G. Johnston]

On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver 
wrote:

> On 6/19/20 6:53 AM, Pepe TD Vo wrote:
> > Thank you sir and I am sorry for the typo not having "--" on password.
> > I did spelling out with --password=mypassword
>
> Please go back and read my post again.
>

To be clear, there is no way to supply a password as a command line
argument.  It is fundamentally a bad idea and we don't even make it an
option.

You need to decide on one of the actual ways of supplying a password, or
choose an alternative authentication method like peer.

David J.

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 7:52 AM, David G. Johnston wrote:
On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver > wrote:


On 6/19/20 6:53 AM, Pepe TD Vo wrote:
 > Thank you sir and I am sorry for the typo not having "--" on
password.
 > I did spelling out with --password=mypassword

Please go back and read my post again.


To be clear, there is no way to supply a password as a command line 
argument.  It is fundamentally a bad idea and we don't even make it an 
option.


Actually that is not entirely true, see my follow up post.



You need to decide on one of the actual ways of supplying a password, or 
choose an alternative authentication method like peer.


David J.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: create batch script to import into postgres tables

[David G. Johnston]

On Fri, Jun 19, 2020 at 7:33 AM Adrian Klaver 
wrote:

> On 6/19/20 7:17 AM, pepevo wrote:
> > I understand your post about "password does not take an argument, it is
> > meant to be used as is. The purpose is to force a password prompt." When
> > I used -W and --password=.  That's what I said I will try pgpassfile.
> > Thought it like mysq/oracle can indicate out without creating password
> > file.
>
> If you want to expose your password in the script file then:
>
> https://www.postgresql.org/docs/12/app-psql.html
>
> Usage
> Connecting to a Database
>
> "An alternative way to specify connection parameters is in a conninfo
> string or a URI, which is used instead of a database name. This
> mechanism give you very wide control over the connection. For example:
>
> $ psql "service=myservice sslmode=require"
> $ psql postgresql://dbmaster:5433/mydb?sslmode=require
>
> This way you can also use LDAP for connection parameter lookup as
> described in Section 33.17. See Section 33.1.2 for more information on
> all the available connection options."
>
> So:
>
> psql
> postgresql://PSmasteruser:mypassw...@hostname.amazonaws.com:5432/PSCIDR
>
> or
>
> psql 'dbname=PSCIDR user=PSmasteruser host=hostname.amazonaws.com port=
> 5432 password=mypassword '
>
>
Ok, so not "no way", but it's still a bad idea given the availability of
other better options.  Namely PGPASSWORD, .pgpass, or, less
desirably.pg_service.conf

The URI format that includes a password should be reserved for client
libraries and avoided when using psql (just the password part really though
I much prefer the service file option myself).

David J.

Re: create batch script to import into postgres tables

[Pepe TD Vo]

appreciate for clarification, all inputs and teaching me more in PostgreSQL
have a good weekend and happy father's day to all who is Father.
v/r, 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver 
 wrote:  
 
 On 6/19/20 7:52 AM, David G. Johnston wrote:
> On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver  > wrote:
> 
>    On 6/19/20 6:53 AM, Pepe TD Vo wrote:
>      > Thank you sir and I am sorry for the typo not having "--" on
>    password.
>      > I did spelling out with --password=mypassword
> 
>    Please go back and read my post again.
> 
> 
> To be clear, there is no way to supply a password as a command line 
> argument.  It is fundamentally a bad idea and we don't even make it an 
> option.

Actually that is not entirely true, see my follow up post.

> 
> You need to decide on one of the actual ways of supplying a password, or 
> choose an alternative authentication method like peer.
> 
> David J.
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com


  

Re: create batch script to import into postgres tables

[cgerard999]

There is an alternate solution, which is to launch pgadmin GUI, connect to the 
database, tick « save password »  
Then psql won’t prompt fir password any more. 
Pay however attention to the security concern. 

Sent from my mobile phone

> Le 19 juin 2020 à 17:07, Pepe TD Vo  a écrit :
> 
> 
> appreciate for clarification, all inputs and teaching me more in PostgreSQL
> 
> have a good weekend and happy father's day to all who is Father.
> 
> v/r, 
> 
> Bach-Nga
> 
> No one in this world is pure and perfect.  If you avoid people for their 
> mistakes you will be alone. So judge less, love, and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he had 
> four legs, a tail, and barked, I admit he was, to all outward appearances. 
> But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
> 
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
> 
> 
> 
> 
> On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver 
>  wrote:
> 
> 
> On 6/19/20 7:52 AM, David G. Johnston wrote:
> > On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver  > > wrote:
> > 
> >On 6/19/20 6:53 AM, Pepe TD Vo wrote:
> >  > Thank you sir and I am sorry for the typo not having "--" on
> >password.
> >  > I did spelling out with --password=mypassword
> > 
> >Please go back and read my post again.
> > 
> > 
> > To be clear, there is no way to supply a password as a command line 
> > argument.  It is fundamentally a bad idea and we don't even make it an 
> > option.
> 
> Actually that is not entirely true, see my follow up post.
> 
> > 
> > You need to decide on one of the actual ways of supplying a password, or 
> > choose an alternative authentication method like peer.
> > 
> > David J.
> 
> > 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
> 

Re: Netapp SnapCenter

[Stephen Frost]

Greetings,

* Paul Förster (paul.foers...@gmail.com) wrote:
> When I wrote our backup mode script I read the deprecation note about the 
> exclusive mode backup. This is why I decided to go with non-exclusive to be 
> ready when exclusive backup mode is finally removed some day. Yet, I don't 
> see the reason. Everything has to be consistent. So a non-exclusive backup 
> mode makes absolutely no sense to me. Either the whole database cluster is in 
> backup mode or it is not. There's nothing in between.

Glad that you went with the non-exclusive method.

When it comes to 'backup mode', it's actually the case that there can be
multiple backups running concurrently because there isn't actually a
single 'cluster wide backup mode', really.  Regarding the deprecated
methodology, there just isn't a way for the database on-disk image to
look exactly like a backup while also being able to survive a
crash/restart with only the WAL that's in the pg_wal directory.  Perhaps
there's other things we could do but at some point it has to be accepted
that there's gotta be something changed in the data directory to
indicate that it's a backup and not just a crash, and that change needs
to happen *after* the backup/snapshot/whatever has been taken, otherwise
there's a window of risk where a crash/restart would fail.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: running a batch script

[Paul Förster]

Hi,

> On 19. Jun, 2020, at 17:30, Richard Bernstein  wrote:
> 

thanks for posting all private data to a public list!

Can some list admin please delete at least the attached script? It contains 
lots of private data.

No wonder, there are millions and millions of leaked personal information 
records on the net if, to put it mildly, some naive people post them publicly. 
Not even a slight hack into some site is needed to get such things these days.

What a world. :-(

Cheers,
Paul

Re: running a batch script

[Richard Bernstein]

You're welcome. It is not real data: It was just a sample.

On Fri, Jun 19, 2020 at 12:08 PM Paul Förster 
wrote:

> Hi,
>
> > On 19. Jun, 2020, at 17:30, Richard Bernstein 
> wrote:
> > 
>
> thanks for posting all private data to a public list!
>
> Can some list admin please delete at least the attached script? It
> contains lots of private data.
>
> No wonder, there are millions and millions of leaked personal information
> records on the net if, to put it mildly, some naive people post them
> publicly. Not even a slight hack into some site is needed to get such
> things these days.
>
> What a world. :-(
>
> Cheers,
> Paul

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 8:30 AM, cgerard...@gmail.com wrote:
There is an alternate solution, which is to launch pgadmin GUI, connect 
to the database, tick « save password »

Then psql won’t prompt fir password any more.


Pretty sure that is only within the context of pgAdmin.


Pay however attention to the security concern.

Sent from my mobile phone


Le 19 juin 2020 à 17:07, Pepe TD Vo  a écrit :


appreciate for clarification, all inputs and teaching me more in 
PostgreSQL


have a good weekend and happy father's day to all who is Father.

v/r,

**
*Bach-Nga

*No one in this world is pure and perfect.  If you avoid people for 
their mistakes you will be alone. So judge less, love, and forgive 
more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as 
he had four legs, a tail, and barked, I admit he was, to all outward 
appearances. But to those who knew him well, he was a perfect 
gentleman (Hermione Gingold)


**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver 
 wrote:



On 6/19/20 7:52 AM, David G. Johnston wrote:
> On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>
> >> wrote:

>
>    On 6/19/20 6:53 AM, Pepe TD Vo wrote:
>      > Thank you sir and I am sorry for the typo not having "--" on
>    password.
>      > I did spelling out with --password=mypassword
>
>    Please go back and read my post again.
>
>
> To be clear, there is no way to supply a password as a command line
> argument.  It is fundamentally a bad idea and we don't even make it an
> option.

Actually that is not entirely true, see my follow up post.

>
> You need to decide on one of the actual ways of supplying a 
password, or

> choose an alternative authentication method like peer.
>
> David J.

>


--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: running a batch script

[Adrian Klaver]

On 6/19/20 8:30 AM, Richard Bernstein wrote:
I am trying to test out a package called phpGrid and I need to run 
through its tutorial. But there are some "kinks". I am developing on my 
ubuntu laptop in a docker container. The postgresql I need to test-to is 
AWS RDS Postgresql.  I have an account and Server and can access it with 
pgAdmin. The install instructs for phpGrid asked me to modify conf.php. 
In my case I used app.conf which sets up the virtual host. I restarted 
the containers. Next the tutorial wants me to "run" an sql batch script 
to create the table. The batch script is pretty mySQL centric. I am 
attaching it.


Can I run this from pgAdmin the same way I would with phpMyAdmin in 
mySQL? Do I need to modify the script for postgresql? Is this the 


Yes. This script is definitely MySQL specific. Some examples:

USE `sampledb`;

CREATE TABLE `changes` (
  `n_r` int(11) NOT NULL AUTO_INCREMENT, <--That would be SERIAL or 
IDENTITY.


) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; <--No go.

UNLOCK TABLES;

and so on.



easiest way to create this sample table? I have one table that I have 


Take a look at:

https://pgloader.readthedocs.io/en/latest/ref/mysql.html

created in pgAdmin called called imagesdatabase and I'd prefer not to 
break that since it took some time with AWS support to get that going.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: create batch script to import into postgres tables

[Pepe TD Vo]

thank you for all the information but I have no problem connecting to the 
database using pgAdmin and/or directly psql from the postgres database.
I need to set up a batch/cron job to run in Linux/AWS to ingest the data.  
Therefore pgadmin GUI is not an option. I used pgAdmin to create tables, 
triggers, function triggers and manual importing/exporting.  You can't schedule 
a time to run importing from  csv file(s) using pgAdmin as same OEM.  
The select table in this question is just an example for me to create scripting 
to see it connect or not.  No one at work to manually run the script and enter 
the password.  Need to bypass it like Oracle scripting.  Since this is not an 
option to put the password to connect to the database and run the script(s), we 
will find another way to do.
I am thankful for all the input.  
v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Friday, June 19, 2020, 02:24:05 PM EDT, Adrian Klaver 
 wrote:  
 
 On 6/19/20 8:30 AM, cgerard...@gmail.com wrote:
> There is an alternate solution, which is to launch pgadmin GUI, connect 
> to the database, tick « save password »
> Then psql won’t prompt fir password any more.

Pretty sure that is only within the context of pgAdmin.

> Pay however attention to the security concern.
> 
> Sent from my mobile phone
> 
>> Le 19 juin 2020 à 17:07, Pepe TD Vo  a écrit :
>>
>> 
>> appreciate for clarification, all inputs and teaching me more in 
>> PostgreSQL
>>
>> have a good weekend and happy father's day to all who is Father.
>>
>> v/r,
>>
>> **
>> *Bach-Nga
>>
>> *No one in this world is pure and perfect.  If you avoid people for 
>> their mistakes you will be alone. So judge less, love, and forgive 
>> more.EmojiEmojiEmoji
>> To call him a dog hardly seems to do him justice though in as much as 
>> he had four legs, a tail, and barked, I admit he was, to all outward 
>> appearances. But to those who knew him well, he was a perfect 
>> gentleman (Hermione Gingold)
>>
>> **Live simply **Love generously **Care deeply **Speak kindly.
>> *** Genuinely rich *** Faithful talent *** Sharing success
>>
>>
>>
>>
>> On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver 
>>  wrote:
>>
>>
>> On 6/19/20 7:52 AM, David G. Johnston wrote:
>> > On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver 
>> mailto:adrian.kla...@aklaver.com>
>> > > >> wrote:
>> >
>> >    On 6/19/20 6:53 AM, Pepe TD Vo wrote:
>> >      > Thank you sir and I am sorry for the typo not having "--" on
>> >    password.
>> >      > I did spelling out with --password=mypassword
>> >
>> >    Please go back and read my post again.
>> >
>> >
>> > To be clear, there is no way to supply a password as a command line
>> > argument.  It is fundamentally a bad idea and we don't even make it an
>> > option.
>>
>> Actually that is not entirely true, see my follow up post.
>>
>> >
>> > You need to decide on one of the actual ways of supplying a 
>> password, or
>> > choose an alternative authentication method like peer.
>> >
>> > David J.
>>
>> >
>>
>>
>> -- 
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>


-- 
Adrian Klaver
adrian.kla...@aklaver.com


  

Re: create batch script to import into postgres tables

[Rob Sargent]

> On Jun 19, 2020, at 1:26 PM, Pepe TD Vo  wrote:
> 
> thank you for all the information but I have no problem connecting to the 
> database using pgAdmin and/or directly psql from the postgres database.
> 
> I need to set up a batch/cron job to run in Linux/AWS to ingest the data.  
> Therefore pgadmin GUI is not an option. I used pgAdmin to create tables, 
> triggers, function triggers and manual importing/exporting.  You can't 
> schedule a time to run importing from  csv file(s) using pgAdmin as same OEM. 
>  
> 
> The select table in this question is just an example for me to create 
> scripting to see it connect or not.  No one at work to manually run the 
> script and enter the password.  Need to bypass it like Oracle scripting.  
> Since this is not an option to put the password to connect to the database 
> and run the script(s), we will find another way to do.
> 
> I am thankful for all the input.  
> 
> v/r,

And it has been explained upstream that one “way to do” is to use a .pgpass 
file for the cronjob user.  And that file must be read-only to user, no access 
to group or other (chmod 400 $CRONUSER/.pgpass).  Another is a “trust” entry 
for the user in pg_hba.conf.  I suggest you revisit those messages. 
 

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 12:26 PM, Pepe TD Vo wrote:
thank you for all the information but I have no problem connecting to 
the database using pgAdmin and/or directly psql from the postgres database.


I need to set up a batch/cron job to run in Linux/AWS to ingest the 
data.  Therefore pgadmin GUI is not an option. I used pgAdmin to create 
tables, triggers, function triggers and manual importing/exporting.  You 
can't schedule a time to run importing from  csv file(s) using pgAdmin 
as same OEM.


There is pgAgent:
https://www.pgadmin.org/docs/pgadmin4/development/pgagent.html



The select table in this question is just an example for me to create 
scripting to see it connect or not.  No one at work to manually run the 
script and enter the password.  Need to bypass it like Oracle 
scripting.  Since this is not an option to put the password to connect 
to the database and run the script(s), we will find another way to do.


But there is an option.



I am thankful for all the input.

v/r,


**
*Bach-Nga

*No one in this world is pure and perfect.  If you avoid people for 
their mistakes you will be alone. So judge less, love, and forgive 
more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he 
had four legs, a tail, and barked, I admit he was, to all outward 
appearances. But to those who knew him well, he was a perfect gentleman 
(Hermione Gingold)


**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Friday, June 19, 2020, 02:24:05 PM EDT, Adrian Klaver 
 wrote:



On 6/19/20 8:30 AM, cgerard...@gmail.com  
wrote:

 > There is an alternate solution, which is to launch pgadmin GUI, connect
 > to the database, tick « save password »
 > Then psql won’t prompt fir password any more.

Pretty sure that is only within the context of pgAdmin.

 > Pay however attention to the security concern.
 >
 > Sent from my mobile phone
 >
 >> Le 19 juin 2020 à 17:07, Pepe TD Vo > a écrit :

 >>
 >> 
 >> appreciate for clarification, all inputs and teaching me more in
 >> PostgreSQL
 >>
 >> have a good weekend and happy father's day to all who is Father.
 >>
 >> v/r,
 >>
 >> **
 >> *Bach-Nga
 >>
 >> *No one in this world is pure and perfect.  If you avoid people for
 >> their mistakes you will be alone. So judge less, love, and forgive
 >> more.EmojiEmojiEmoji
 >> To call him a dog hardly seems to do him justice though in as much as
 >> he had four legs, a tail, and barked, I admit he was, to all outward
 >> appearances. But to those who knew him well, he was a perfect
 >> gentleman (Hermione Gingold)
 >>
 >> **Live simply **Love generously **Care deeply **Speak kindly.
 >> *** Genuinely rich *** Faithful talent *** Sharing success
 >>
 >>
 >>
 >>
 >> On Friday, June 19, 2020, 10:57:59 AM EDT, Adrian Klaver
 >> mailto:adrian.kla...@aklaver.com>> wrote:
 >>
 >>
 >> On 6/19/20 7:52 AM, David G. Johnston wrote:
 >> > On Fri, Jun 19, 2020 at 6:58 AM Adrian Klaver
 >> mailto:adrian.kla...@aklaver.com> 
>

 >> > 
 >> > >
 >> >    On 6/19/20 6:53 AM, Pepe TD Vo wrote:
 >> >      > Thank you sir and I am sorry for the typo not having "--" on
 >> >    password.
 >> >      > I did spelling out with --password=mypassword
 >> >
 >> >    Please go back and read my post again.
 >> >
 >> >
 >> > To be clear, there is no way to supply a password as a command line
 >> > argument.  It is fundamentally a bad idea and we don't even make it an
 >> > option.
 >>
 >> Actually that is not entirely true, see my follow up post.
 >>
 >> >
 >> > You need to decide on one of the actual ways of supplying a
 >> password, or
 >> > choose an alternative authentication method like peer.
 >> >
 >> > David J.

 >>
 >> >
 >>
 >>
 >> --
 >> Adrian Klaver
 >> adrian.kla...@aklaver.com  
>

 >>
 >>


--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Minor Upgrade Question

[Susan Joseph]

OK I will try that, thanks

On 6/17/2020 11:19 AM, Joshua Drake wrote:

Susan

You can use -Uvh to upgrade the rpms on the existing machine. You can 
then use symlinks to link the expected pgsql data directories. Make 
sure you take a backup, and stop the service before you proceed.


JD


On Tue, Jun 16, 2020 at 7:12 AM Susan Joseph > wrote:


So when I first started working with PostgreSQL I was using the
latest version (11.2).   I don't want to move to 12 yet but I
would like to get my 11.2 up to 11.8.  Due to my servers not being
connected to the Internet I ended up downloading the libraries and
building the files locally.  My question is how do I upgrade to
11.8?  I know how to go and get the rpms now and download those to
a disconnected server and then install PostgreSQL that way.  I was
able to install 11.8 on another server using the rpms.  But my
directories are different.  The rpm install placed the files into
the directory /data/pgsql-11.  My 11.2 database is in
/data/pgsql.  I checked the rpm file and it says that the files
are not relocatable.  So I can do a new install of 11.8 via the
rpms and it will place the files into /data/pgsql-11, can just
need to copy the executable files in the /data/pgsql-11/bin
directory into my /data/pgsql/bin or are there other files that
need to be copied over?  Is there a better way to do this rather
than reinstalling postgreSQL again on a server that already has it?

Thanks,
  Susan

Re: create batch script to import into postgres tables

[David G. Johnston]

On Friday, June 19, 2020, pepevo  wrote:

> We can't just install any softwares without Goverment's approval.  Also,
> they might ask Oracle/mysql/sql can run batch script, why not Postgres?  I
> wonder myself and just realize today from this email.
>

PostreSQL isn’t the issue here, you are.  To the extent that others
attempting to help you over email are insufficient is a failing driven
mainly by the medium of choice probably being an inefficient medium for the
student.  What you want to do is possible but it requires understanding
multiple applications.  You are better off getting closer to your goal by
reading books and articles about those applications and then asking better
questions.  Or at minimum being more detailed in stating your goal and
requirements.

David J.

Re: create batch script to import into postgres tables

[David G. Johnston]

On Friday, June 19, 2020, pepevo  wrote:

> But everything can run by script on the server, right?
>

Separation of concerns.  The server with the database cluster should
probably not be running application code.  Application code can be run
other machine, “admin” machine is one label.  Though for development it
shouldn’t matter so long as the application is configurable.  Configure it
for local during development and when in production it pulls production
configuration.

David J.

[HELP] Query regarding logical replication slot

[Praveen Kumar K S]

Hello,

I'm running Postgres9.6 and configured master/slave with synchronous
replication (synchronous_commit set to remote_apply) .
In addition to that I have one logical replication slot on master. When
master fails and one of the slaves is promoted to master, how can I have
that logical replication slot and LSN on the slave ?

-- 


*Regards,*


*K S Praveen Kumar*

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi Laurenz,

> Yes, that are conflicts with VACUUM.

Thank you for help. hot_standby_feedback did what I expected (no lag on 
replication and no session termination) and case is closed. Only it is foggy 
for me how it can be when no logs about vacuuming from that time window when 
session is active on standby host.

BR,
Toomas

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/19/20 6:28 PM, pepevo wrote:
Yes, and I am thank you for all your helps.  As I said and if you 
contract for Govt you should know,  we can't just install the software 
without their approval.   When someone suggested psql client to run 
batch script when I can't putty into the AWS instance.  I need to asked 
lead to approval to download even I don't have administrative  
privileges and because it's on Dev and  it's on our server.  Real work 
will be on Linux production and everything need to be approved by 
Government.  But everything can run by script on the server, right?  


Alright now I am thoroughly confused. You where asking how to connect 
from a Windows using psql to run a batch script from Windows machine. No 
mention of any government restrictions, or approval to download. Also, 
now you say you will be working on Linux machine and the script will run 
on server. So basically your original question has nothing to do with 
reality. Correct me if I am wrong.


Also, I will take a look on pgpass per Adrian's suggestion when I am 
back onsite.   I am still learning Postgres here and have limited on the 
server.


I appreciated for all the helps here.

Bach-Nga

Sent from my Metro By T-Mobile 4G LTE Android Device


 Original message 
From: "David G. Johnston" 
Date: 6/19/20 20:24 (GMT-05:00)
To: pepevo 
Cc: Adrian Klaver , cgerard...@gmail.com, 
Christopher Browne , Pgsql-admin 
, Pgsql-general 

Subject: Re: create batch script to import into postgres tables

On Friday, June 19, 2020, pepevo > wrote:


We can't just install any softwares without Goverment's approval. 
Also, they might ask Oracle/mysql/sql can run batch script, why not
Postgres?  I wonder myself and just realize today from this email. 



PostreSQL isn’t the issue here, you are.  To the extent that others 
attempting to help you over email are insufficient is a failing driven 
mainly by the medium of choice probably being an inefficient medium for 
the student.  What you want to do is possible but it requires 
understanding multiple applications.  You are better off getting closer 
to your goal by reading books and articles about those applications and 
then asking better questions.  Or at minimum being more detailed in 
stating your goal and requirements.


David J.




--
Adrian Klaver
adrian.kla...@aklaver.com