Question about index on different tablespace and rebuild it

[Condor]

Hello,

I have a database that use index on different table space (nvme). I read 
documentation about table space and understand table space cannot be 
treated as an autonomous collection of data files.
My question is: Is this always true ? I mean if I have table .. okay 
here is example:


create table t1 ( i integer);
create index t1_i_idx on t1 using btree (i) tablespace nvme;

In this case only the index is on different table space nvme that is not 
part of data stored into table, I mean if I drop the index, data stored 
in table will still be untouched and not damaged.
So in this case if I lost table space nvme that is stored into different 
nvme drive, can I insert new one and rebuild all index files with 
reindexdb for example or some other tool like
mysql/mariadb for example myismcheck or something like that, that can 
rebuild index files when DB sever is offline ?



Regards,
HS

Re: Logical replication hangs up.

[Achilleas Mantzios]

On 3/11/18 6:02 μ.μ., Jerry Sievers wrote:

Aleš Zelený  writes:


Hello,

we are suing logical replication on 10.4  and it now hangs. After
some timeout it is retarted again, replaying 18GB of data and then
hangs (while 7GB of wals remains to be proceeded).

Timeout...

Have a look at the 2 setting wal sender/receiver timeout and you
probably need to raise the sender timeout value.

That's been the case for me as well, setting both values to '5 mins' solved my 
issues.


HTH


The backlog of 18GB comes from a failed migration adding new table to
replication while replication user was not granted to select the
table. This was clear from log files and once resolved by adding
select privilege, I thought that all will work as usual (same
happened in test env. many times and adding missing grant for select
was sufficient to get it working... these were issues on tests).

RDBMS Version:
PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609,
64-bit

Publication (master) instance error message from log:

2018-11-02 18:34:14 UTC 7974 5bdc8d27.1f26 7 192.168.23.11(58884)
master_prod repusr 0 5/0 sub_eur idle [0]:LOG:  terminating
walsender process due to replication timeout

Subscription instance log:

2018-11-02 18:34:14 UTC 8657 5bdc8d26.21d1 2    0 3/0
[XX000]:ERROR:  could not receive data from WAL stream: SSL
connection has been closed unexpectedly
2018-11-02 18:34:14 UTC 24699 5b923b1c.607b 1209397    0
[0]:LOG:  worker process: logical replication worker for
subscription 37932 (PID 8657) exited with exit code 1

While it seems to be a network issue, it is may be not - we have
checked the network and even monitoring, all the time some packets
were exchanged.

We do have 3 subscriptions(thus repl. slots) for one publication, one
subscriber instance is within same datacenter as master, remainig
subscribers are remote.



select * from pg_replication_slots

++--+---++-+---+++--+--+--+-+
| slot_name  | plugin   | slot_type | datoid | database    |
temporary | active | active_pid | xmin | catalog_xmin | restart_lsn
| confirmed_flush_lsn |
++--+---++-+---+++--+--+--+-+
| sub_usd    | pgoutput | logical   | 16421  | master_prod |
False | True   | 16604  |  | 5536488  | 426/AAE55A68
| 426/AAE55A68    |
| sub_cad    | pgoutput | logical   | 16421  | master_prod |
False | True   | 22875  |  | 5536488  | 426/AAE55A68
| 426/AAE55A68    |
| sub_eur    | pgoutput | logical   | 16421  | master_prod |
False | True   | 16605  |  | 5536488  | 426/AAE55A68
| 426/AAE55A68    |
++--+---++-+---+++--+--+--+-+


Once  after the error occurs, wal senders are re/started and they
reads wal files until they reach restart_lsn wal file:


select pg_walfile_name('426/AAE55A68')

+--+
| pg_walfile_name  |
+--+
| 0001042600AA |
+--+

# changing file names until they reach this one:
root@master-db:/pgsql/pgcluster/10/master_prod# lsof -p 1560 -p 5758
-p 5790| grep pg_wal
postgres 1560 postgres   10r  REG  259,3  16777216 115766007 /
pgsql/pgcluster/10/master_prod/pg_wal/000104290069
postgres 5758 postgres   10r  REG  259,3  16777216 115766007 /
pgsql/pgcluster/10/master_prod/pg_wal/000104290069
postgres 5790 postgres   10r  REG  259,3  16777216 115766007 /
pgsql/pgcluster/10/master_prod/pg_wal/000104290069


At this moment sent_lsn stops growing and nothing is happening for a
while.

select * from pg_stat_replication;

   pid  | usesysid | usename |  application_name  | client_addr   |
client_hostname | client_port | backend_start |
backend_xmin |  state  |   sent_lsn   |  write_lsn   |  flush_lsn   |
   replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority |
sync_state
---+--+-++---+-+-+---+--+-+--+--+--+--+---+---++---+
  16604 |    37868 | repusr  | sub_usd    | 192.168.21.11 |
  |   35010 | 2018-11-02 23:52:22.059157+00 |
   | catchup | 429/69E9CC60 | 426/AAE55A68 | 426/AAE55A68
| 426/AAE55A68 |   |   |    | 0 |
async
  16605 |    37868 | repusr  | sub_eur    | 192.168.23.11 |
  |   36388 | 2018-11-02 23:52:24.75038+00  |
   | ca

Re: Password management in PostgreSQL

[pavan95]

Hello Community,

I wanted to know if password can be managed by the respective users at the
time of their login, after their password has been expired?

Consider the following case:

*postgres=#  create user pavan with login password 'password' valid until
'November 5 16:30:00 2018';*
CREATE ROLE
\q

/>psql -d postgres -U pavan
Password for user pavan:

postgres=> select current_timestamp;
  now
---
* 2018-11-05 15:31:11.394+05:30*


\q

*Now at : 2018-11-05 16:31:11.394+05:30*

/>psql -d postgres -U pavan
Password for user pavan: 
*psql: FATAL:  password authentication failed for user "pavan"*

Now due to that "VALID UNTIL" option password got expired at 16:30 Hrs. 

Now how can I ask user "pavan" to change his password at the time of login
without administrator intervening into it??

*I have checked with Oracle team, it provides this facility in-built.  I
hope postgres is not provided with this type of password management
mechanism.

If so, can we think of a alternative wherein the corresponding user can be
enable to change his/her password??*

Looking forward to hear your valuable suggestions. Thanks in advance.


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Function for Exception Logging

[Alexey Bashtanov]

Even more difficult in PG functions as they have no commit / rollback 
capability.  I haven't played with stored procedures in in PG11 yet.


You can simulate oracle autonomous transaction feature in postgres by 
connecting to the same db using dblink.


As for implicit passing of error parameters, I don't think it's possible.

Best, Alex

pgAgent on Windows

[Marcio Meneguzzi]

Hello,

I´m trying use pgAgent on windows 10 and Windows Server 2012 R2.
My version of PostgreSQL is 9.5.1.14
Install and configure pgAgent with sucess, but, when I try run a Job,
status is Failed with a message bellow:

*"Couldn't create the primary connection (attempt 1): fe_sendauth: no
password supplied"*

My %APPDATA%\postgresql\pg_pass.conf is:
localhost:5490:*:postgres:my_pass

Please, any help?

Thank´s,
Márcio.

Re: Trouble Upgrading Postgres

[Charles Martin]

Tom said:

>That's kind of odd: a COPY shouldn't really consume very much working
>memory.  I suspect that much of the process's apparent VM consumption may
>be shared buffers ... what have you got shared_buffers set to on the old
>server?  If it's more than half a GB or so, maybe reducing it would help.

This is not a VM, but hardware.

Adrian said:

>In addition to the other suggestions, what is the exact pg_dump command
>you are using?

The last time:

[postgres@mandj tmp]$ pg_dump martinandjones >
/mnt/4tbB/pgbackup/2018-11-02/mandj.bak



On Sun, Nov 4, 2018 at 8:16 PM Adrian Klaver 
wrote:

> On 11/4/18 2:55 PM, Charles Martin wrote:
> > Yep, you called it:
> >
> > Nov  2 20:30:45 localhost kernel: Out of memory: Kill process 30438
> > (postmaster) score 709 or sacrifice child
> > Nov  2 20:30:45 localhost kernel: Killed process 30438, UID 26,
> > (postmaster) total-vm:3068900kB, anon-rss:1695392kB, file-rss:1074692kB
> >
> > So it's running out of memory when trying to dump this table. The "old"
> > server has 4GB of ram, the "new" server 20GB.
> >
>
> In addition to the other suggestions, what is the exact pg_dump command
> you are using?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Trouble Upgrading Postgres

[Adrian Klaver]

On 11/5/18 5:56 AM, Charles Martin wrote:

Tom said:

 >That's kind of odd: a COPY shouldn't really consume very much working
 >memory.  I suspect that much of the process's apparent VM consumption may
 >be shared buffers ... what have you got shared_buffers set to on the old
 >server?  If it's more than half a GB or so, maybe reducing it would help.

This is not a VM, but hardware.


Tom was referring to this from your previous post:

(postmaster) total-vm:3068900kB,

where vm(VM) is Virtual Memory:

https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766

So what is your shared_buffers:

https://www.postgresql.org/docs/10/static/runtime-config-resource.html

set to?



Adrian said:

 >In addition to the other suggestions, what is the exact pg_dump command
 >you are using?

The last time:

[postgres@mandj tmp]$ pg_dump martinandjones > 
/mnt/4tbB/pgbackup/2018-11-02/mandj.bak





On Sun, Nov 4, 2018 at 8:16 PM Adrian Klaver > wrote:


On 11/4/18 2:55 PM, Charles Martin wrote:
 > Yep, you called it:
 >
 > Nov  2 20:30:45 localhost kernel: Out of memory: Kill process 30438
 > (postmaster) score 709 or sacrifice child
 > Nov  2 20:30:45 localhost kernel: Killed process 30438, UID 26,
 > (postmaster) total-vm:3068900kB, anon-rss:1695392kB,
file-rss:1074692kB
 >
 > So it's running out of memory when trying to dump this table. The
"old"
 > server has 4GB of ram, the "new" server 20GB.
 >

In addition to the other suggestions, what is the exact pg_dump command
you are using?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Trouble Upgrading Postgres

[Daniel Verite]

Charles Martin wrote:

> >So where is the server located relative to the pg_dump client?
> >On the same machine?
> >If so is it a virtual machine e.g AWS?
> >Across a local or remote network?
> 
> 
> I gave the command in a terminal session after SSHing to the server from
> the same network. It is not a virtual machine.

That means that pg_dump runs on the same machine as the server.

It's plausible that, with only 4GB of RAM, the table that fails
to dump has some very large rows that can't be allocated, especially
since both the backend and pg_dump need to have it simultaneously
in memory.

> pg_dump: The command was: COPY public.docfile (docfile_pkey, 
> docfileoriginalname, ordernumber, versionnum, docfilecontents, 
> docfilepath, docfileextension, enddatetime, endby, editnum, insby, 
> insdatetime, modby, moddatetime, active, doc_fkey) TO stdout;

The "docfilecontents" column suggests that it might contain
large contents. If its type is bytea, it's going to be expanded
to twice its size to build the hex representation.

You may get a sense on how big is the biggest row expressed
as text with this query:
 SELECT max(length(contents.*::text)) FROM public.docfile;

If it's big enough that it might cause the OOM issue,
try to run pg_dump remotely through an SSH tunnel [1], which you
can already do in terms of network permissions since you log in with
SSH, so pg_dump itself does not use any memory on the server.

Also, if the machine doesn't have swap space, it might be
that just adding a few GB's of swap would make the operation
succeed.


[1] https://www.postgresql.org/docs/current/static/ssh-tunnels.html

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Trouble Upgrading Postgres

[Charles Martin]

Adrian said:

>Tom was referring to this from your previous post:
>(postmaster) total-vm:3068900kB,
>where vm(VM) is Virtual Memory:
>
https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766
>So what is your shared_buffers:
>https://www.postgresql.org/docs/10/static/runtime-config-resource.html
>set to?

Ok, thanks for explaining this. Here is the current value:
"shared_buffers" "131072" "8kB"



On Mon, Nov 5, 2018 at 9:06 AM Adrian Klaver 
wrote:

> On 11/5/18 5:56 AM, Charles Martin wrote:
> > Tom said:
> >
> >  >That's kind of odd: a COPY shouldn't really consume very much working
> >  >memory.  I suspect that much of the process's apparent VM consumption
> may
> >  >be shared buffers ... what have you got shared_buffers set to on the
> old
> >  >server?  If it's more than half a GB or so, maybe reducing it would
> help.
> >
> > This is not a VM, but hardware.
>
> Tom was referring to this from your previous post:
>
> (postmaster) total-vm:3068900kB,
>
> where vm(VM) is Virtual Memory:
>
>
> https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766
>
> So what is your shared_buffers:
>
> https://www.postgresql.org/docs/10/static/runtime-config-resource.html
>
> set to?
>
> >
> > Adrian said:
> >
> >  >In addition to the other suggestions, what is the exact pg_dump command
> >  >you are using?
> >
> > The last time:
> >
> > [postgres@mandj tmp]$ pg_dump martinandjones >
> > /mnt/4tbB/pgbackup/2018-11-02/mandj.bak
> >
> >
> >
> >
> > On Sun, Nov 4, 2018 at 8:16 PM Adrian Klaver  > > wrote:
> >
> > On 11/4/18 2:55 PM, Charles Martin wrote:
> >  > Yep, you called it:
> >  >
> >  > Nov  2 20:30:45 localhost kernel: Out of memory: Kill process
> 30438
> >  > (postmaster) score 709 or sacrifice child
> >  > Nov  2 20:30:45 localhost kernel: Killed process 30438, UID 26,
> >  > (postmaster) total-vm:3068900kB, anon-rss:1695392kB,
> > file-rss:1074692kB
> >  >
> >  > So it's running out of memory when trying to dump this table. The
> > "old"
> >  > server has 4GB of ram, the "new" server 20GB.
> >  >
> >
> > In addition to the other suggestions, what is the exact pg_dump
> command
> > you are using?
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Trouble Upgrading Postgres

[Adrian Klaver]

On 11/5/18 7:04 AM, Charles Martin wrote:

Adrian said:

 >Tom was referring to this from your previous post:
 >(postmaster) total-vm:3068900kB,
 >where vm(VM) is Virtual Memory:
 
>https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766
 >So what is your shared_buffers:
 >https://www.postgresql.org/docs/10/static/runtime-config-resource.html
 >set to?

Ok, thanks for explaining this. Here is the current value:
"shared_buffers""131072""8kB"



It should be a single value something like this for the default:

shared_buffers = 128MB


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

Re: Trouble Upgrading Postgres

[Tom Lane]

Charles Martin  writes:
> Ok, thanks for explaining this. Here is the current value:
> "shared_buffers" "131072" "8kB"

Well, that's 1GB, which might be ambitious inside a VM with a hard
restriction to 4GB total RAM.  Postgres can get by with a *lot* less.
Try knocking it down to a tenth of that and see if it makes a difference.

regards, tom lane

Re: ERROR: found multixact from before relminmxid

[Adrien NAYRAT]

On 6/8/18 8:30 PM, Jeremy Finzel wrote:


  No I was referring to this from the documentation:

Avoid spuriously marking pages as all-visible (Dan Wood, Pavan
Deolasee, Álvaro Herrera)

This could happen if some tuples were locked (but not deleted).
While queries would still function correctly, vacuum would normally
ignore such pages, with the long-term effect that the tuples were
never frozen. In recent releases this would eventually result in
errors such as "found multixact n from before relminmxid n".




Hello hackers,

We got the same issue on a 9.6.10, autovacuum reports the same error 
when he tried to freeze a table:

ERROR:  found multixact 37009963 from before relminmxid 48606990

autovacuum was not able to freeze this table until postgres reach 1 
million transactions before wraparound and refuse any new transaction.


We have an OLTP workload and I noticed queries like SELECT .. FOR SHARE. 
I checked durability settings (fsync, fpw) everything seems fine. Also, 
I did not notice any error on the storage (local ssd with raid controler).


We "solved" with a dump/restore on another server, also we kept previous 
cluster to investigate. I want to be sure we encountered the bug solved 
in 9.6.9 and it is not a new one.


If we confirm it, maybe we should advise users to perform integrity 
check? I was surprised 9.6.9 avoid new appearance of corruption but 
nothing to ensure if database is already corrupted.


FYI, we tried to do a pgdump on secondary:
pg_dump: Error message from server: ERROR:  could not access status of 
transaction 1323321209

DETAIL:  Could not open file "pg_clog/04EE": No such file or directory.

I am surprised this clog file is missing on the secondary (but present 
on primary) :

[...]
04EB
04EC
04ED
0CEE
0CEF
0CF0
0CF1
[...]

I ran pg_visibility's checks, they do not reveal any corruption :
select pg_check_visible(i) from (select nspname ||'.' || relname as i 
from pg_class join pg_namespace on 
pg_class.relnamespace=pg_namespace.oid WHERE relkind IN ('r','t','m')) as i;

(0 rows)

select pg_check_frozen(i) from (select nspname ||'.' || relname as i 
from pg_class join pg_namespace on 
pg_class.relnamespace=pg_namespace.oid WHERE relkind IN ('r','t','m')) as i;

(0 rows)

Here is pg_controldata and information on the table where autovacuum 
reports error :

/usr/pgsql-9.6/bin/pg_controldata
pg_control version number:960
Catalog version number:   201608131
Database system identifier:   6451990604639161176
Database cluster state:   in production
pg_control last modified: Mon 05 Nov 2018 12:44:30 PM CET
Latest checkpoint location:   B9AF/70A4FD98
Prior checkpoint location:B9AF/70A4B7D0
Latest checkpoint's REDO location:B9AF/70A4FD60
Latest checkpoint's REDO WAL file:0001B9AF0070
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  1:1323325854
Latest checkpoint's NextOID:  1665327960
Latest checkpoint's NextMultiXactId:  60149587
Latest checkpoint's NextMultiOffset:  174269996
Latest checkpoint's oldestXID:3471809503
Latest checkpoint's oldestXID's DB:   16393
Latest checkpoint's oldestActiveXID:  1323325854
Latest checkpoint's oldestMultiXid:   48606990
Latest checkpoint's oldestMulti's DB: 16393
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:Mon 05 Nov 2018 12:44:29 PM CET
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline:   0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
wal_level setting:replica
wal_log_hints setting:off
max_connections setting:  750
max_worker_processes setting: 8
max_prepared_xacts setting:   0
max_locks_per_xact setting:   64
track_commit_timestamp setting:   off
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Size of a large-object chunk: 2048
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0

SELECT * FROM pg_class WHERE oid = 'anonymised'::regclass;
-[ RECORD 1 
]---+---

relname | a

Re: Trouble Upgrading Postgres

[Charles Martin]

Daniel said:

>It's plausible that, with only 4GB of RAM, the table that fails
to dump has some very large rows that can't be allocated, especially
since both the backend and pg_dump need to have it simultaneously
in memory.
> >pg_dump: The command was: COPY public.docfile (docfile_pkey,
> >docfileoriginalname, ordernumber, versionnum, docfilecontents,
>> docfilepath, docfileextension, enddatetime, endby, editnum, insby,
>> insdatetime, modby, moddatetime, active, doc_fkey) TO stdout;
>The "docfilecontents" column suggests that it might contain
large contents. If its type is bytea, it's going to be expanded
to twice its size to build the hex representation.
>You may get a sense on how big is the biggest row expressed
as text with this query:
 SELECT max(length(contents.*::text)) FROM public.docfile;
>If it's big enough that it might cause the OOM issue,
try to run pg_dump remotely through an SSH tunnel [1], which you
can already do in terms of network permissions since you log in with
SSH, so pg_dump itself does not use any memory on the server.
>Also, if the machine doesn't have swap space, it might be
that just adding a few GB's of swap would make the operation
succeed.

This appears to be the case. I ran:

SELECT max(length(docfilecontents::text)) FROM docfile;
 and after a very long time, got:
ERROR: invalid memory alloc request size 1636085512 SQL state: XX000

Adrian said:
>> Ok, thanks for explaining this. Here is the current value:
>> "shared_buffers""131072""8kB"
>It should be a single value something like this for the default:
>shared_buffers = 128MB

The results I pasted were from:

SELECT * FROM pg_settings

Maybe I didn't get it the right way.

The system has only 4GB of RAM. I read that a reasonable swap size is 1/4
of RAM, so I've created a swap file of 1GB.

Tom said:
>> Ok, thanks for explaining this. Here is the current value:
>> "shared_buffers" "131072" "8kB"
>Well, that's 1GB, which might be ambitious inside a VM with a hard
restriction to 4GB total RAM.  Postgres can get by with a *lot* less.
>Try knocking it down to a tenth of that and see if it makes a difference

I think I also based this on a rule-of-thumb that it should be no more than
25% of RAM. Should I test pg_dump with the added VM before reducing
shared_buffers?

On Mon, Nov 5, 2018 at 10:13 AM Adrian Klaver 
wrote:

> On 11/5/18 7:04 AM, Charles Martin wrote:
> > Adrian said:
> >
> >  >Tom was referring to this from your previous post:
> >  >(postmaster) total-vm:3068900kB,
> >  >where vm(VM) is Virtual Memory:
> >  >
> https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766
> >  >So what is your shared_buffers:
> >  >https://www.postgresql.org/docs/10/static/runtime-config-resource.html
> >  >set to?
> >
> > Ok, thanks for explaining this. Here is the current value:
> > "shared_buffers""131072""8kB"
> >
>
> It should be a single value something like this for the default:
>
> shared_buffers = 128MB
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Trouble Upgrading Postgres

[Ron]

On 11/05/2018 10:50 AM, Charles Martin wrote:
[snip]

The results I pasted were from:

SELECT * FROM pg_settings

Maybe I didn't get it the right way.


We were expecting the output of the "SHOW SHARED_BUFFERS;" command.



The system has only 4GB of RAM. I read that a reasonable swap size is 1/4 
of RAM, so I've created a swap file of 1GB.


Times have changed...  (I'd have made a 4GB swap file.)


--
Angular momentum makes the world go 'round.

Re: Trouble Upgrading Postgres

[Charles Martin]

Ron said:

>We were expecting the output of the "SHOW SHARED_BUFFERS;" command.

Ok, the result from that command is:

1GB

>The system has only 4GB of RAM. I read that a reasonable swap size is 1/4
of RAM, so I've created a swap file of 1GB.

>Times have changed...  (I'd have made a 4GB swap file.)

I have a spare drive that is 230G, so I have enough space. I suppose I can
set swapoff, delete the swapfile, create a new 4G one, and set swapon. Or
is there a better way?

On Mon, Nov 5, 2018 at 11:56 AM Ron  wrote:

> On 11/05/2018 10:50 AM, Charles Martin wrote:
> [snip]
>
> The results I pasted were from:
>
> SELECT * FROM pg_settings
>
> Maybe I didn't get it the right way.
>
>
> We were expecting the output of the "SHOW SHARED_BUFFERS;" command.
>
>
> The system has only 4GB of RAM. I read that a reasonable swap size is 1/4
> of RAM, so I've created a swap file of 1GB.
>
>
> Times have changed...  (I'd have made a 4GB swap file.)
>
>
> --
> Angular momentum makes the world go 'round.
>

Re: Trouble Upgrading Postgres

[Ron]

On 11/05/2018 11:06 AM, Charles Martin wrote:

Ron said:

>We were expecting the output of the "SHOW SHARED_BUFFERS;" command.

Ok, the result from that command is:

1GB
>The system has only 4GB of RAM. I read that a reasonable swap size is 1/4 of 
RAM, so I've created a swap file of 1GB.

>Times have changed...  (I'd have made a 4GB swap file.)

I have a spare drive that is 230G, so I have enough space. I suppose I can 
set swapoff, delete the swapfile, create a new 4G one, and set swapon. Or 
is there a better way?


Linux already knows about the 1GB file; just add a 3GB swap file on the 
230GB drive.


--
Angular momentum makes the world go 'round.

Re: Trouble Upgrading Postgres

[Daniel Verite]

Charles Martin wrote:

> SELECT max(length(docfilecontents::text)) FROM docfile;
> and after a very long time, got:
> ERROR: invalid memory alloc request size 1636085512 SQL state: XX000

It would mean that at least one row has a "docfilecontents"
close to 0.5GB in size. Or that the size fields in certain rows
are corrupted, although that's less plausible if you have
no reason to suspect hardware errors.

Does the following query work:

 SELECT max(octet_length(docfilecontents)) FROM docfile;

or maybe a histogram by size in hundred of megabytes:

 SELECT octet_length(docfilecontents)/(1024*1024*100),
  count(*)
  FROM docfile
  GROUP BY octet_length(docfilecontents)/(1024*1024*100);

Note that the error message above does not say that there's not enough
free memory, it says that it won't even try to allocate that much, because
1636085512 is over the "varlena limit" of 1GB.
AFAICS I'm afraid that this table as it is now cannot be exported
by pg_dump, even if you had enough free memory, because any individual
row in COPY cannot exceed 1GB in text format.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Trouble Upgrading Postgres

[Charles Martin]

The first query timed out, but the second one returned this:

0 "623140"
1 "53"
2 "12"
3 "10"
4 "1"
5 "1"
7 "1"
[null] "162"

Not quite sure what that means, but if there is just a small number of
overly-large records, I might be able to delete them. If I can find them.

On Mon, Nov 5, 2018 at 12:54 PM Daniel Verite 
wrote:

> Charles Martin wrote:
>
> > SELECT max(length(docfilecontents::text)) FROM docfile;
> > and after a very long time, got:
> > ERROR: invalid memory alloc request size 1636085512 SQL state: XX000
>
> It would mean that at least one row has a "docfilecontents"
> close to 0.5GB in size. Or that the size fields in certain rows
> are corrupted, although that's less plausible if you have
> no reason to suspect hardware errors.
>
> Does the following query work:
>
>  SELECT max(octet_length(docfilecontents)) FROM docfile;
>
> or maybe a histogram by size in hundred of megabytes:
>
>  SELECT octet_length(docfilecontents)/(1024*1024*100),
>   count(*)
>   FROM docfile
>   GROUP BY octet_length(docfilecontents)/(1024*1024*100);
>
> Note that the error message above does not say that there's not enough
> free memory, it says that it won't even try to allocate that much, because
> 1636085512 is over the "varlena limit" of 1GB.
> AFAICS I'm afraid that this table as it is now cannot be exported
> by pg_dump, even if you had enough free memory, because any individual
> row in COPY cannot exceed 1GB in text format.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>

Re: Logical replication hangs up.

[Aleš Zelený]

Hello, thanks for sharing the hint,

I've tested 10 minutes, but it did not fix that.

Having more time waiting for timeout, I've run strace on wal sender once
more and now the patern is different (probably I've catch it first time
before it reaches issue point).

There are repeated failing attempts to open certain files for the slot
which are not there:

open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6200.snap",
O_RDONLY) = 28
read(28, "\334\0\0\0\0\0\0\0H]Tb)\4\0\0\0\0\0\0\0\0\0\0\177\6\0\0%@\0\0"...,
72) = 72
read(28,
"|\0\0\0\377\377\377\377\0\0\0\0\0\0\0\0\320\346\37\17\316U\0\0\0\0\0\0\0\0\0\0"...,
148) = 148
read(28, "", 72)= 0
close(28)   = 0
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6300.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6400.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6500.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6600.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6700.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6800.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6900.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6A00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6B00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6C00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6D00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6E00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-6F00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7000.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7100.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7200.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7300.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7400.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7500.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7600.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7700.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7800.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489824-lsn-429-7900.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6200.snap",
O_RDONLY) = 28
read(28, "\334\0\0\0\0\0\0\0 ^Tb)\4\0\0\0\0\0\0\0\0\0\0\177\6\0\0%@\0\0"...,
72) = 72
read(28, "|\0\0\0\377\377\377\377\0\0\0\0\0\0\0\0 \7
\17\316U\0\0\0\0\0\0\0\0\0\0"..., 148) = 148
read(28, "", 72)= 0
close(28)   = 0
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6300.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6400.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6500.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6600.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6700.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6800.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6900.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6A00.snap",
O_RDONLY) = -1 ENOENT (No such file or directory)
open("pg_replslot/sub_anl_master_usd/xid-6489825-lsn-429-6B00.snap",
O_RDONLY) = -1 ENOENT (No su

Re: Trouble Upgrading Postgres

[Adrian Klaver]

On 11/5/18 8:50 AM, Charles Martin wrote:

Daniel said:




This appears to be the case. I ran:

SELECT max(length(docfilecontents::text)) FROM docfile;
  and after a very long time, got:
ERROR: invalid memory alloc request size 1636085512 SQL state: XX000

Adrian said:

Ok, thanks for explaining this. Here is the current value:
"shared_buffers""131072""8kB"

 >It should be a single value something like this for the default:
 >shared_buffers = 128MB

The results I pasted were from:

SELECT * FROM pg_settings

Maybe I didn't get it the right way.

The system has only 4GB of RAM. I read that a reasonable swap size is 
1/4 of RAM, so I've created a swap file of 1GB.


I would change the shared_buffers setting to 128MB and retry.





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

CREATE OR REPLACE FUNCTION statement just sitting there

[Ron]

I've got a very puzzling problem on 9.6.6 systems we just migrated from 
8.4.  (The same problem happened on 9.6.9, but rolled it back so as to make 
prod have the same version as our Staging systems.)


We've got a giant script full of DROP TRIGGER IF EXISTS and CREATE TABLE and 
DROP TABLE and CREATE OR REPLACE FUNCTION statements.


It's purpose is to drop old parts of partitioned tables and add new tables.

It *ALWAYS worked* just fine on our big, ancient, production 8.4 databases 
(otherwise I'd have heard the screams of user rage), and on our 9.6.6 
staging environment.  However, one or more of our big (and schema-identical) 
prod databases (which are each on a different server) it is finicky and 
tends to just "sit" at a random one of the CREATE OR REPLACE FUNCTION 
statements.


The "list all blocking queries" I run doesn't show that anything is blocking 
it (though it blocks everything else), and neither top(1) nor iotop(1) show 
any activity.


If it matters, this script is fed to the databases via the JDBC driver, and 
it works fine when I run it via psql.  (I'd gladly run the scripts manually, 
but these are child databases, and a parent db must be updated at the same 
time by a canned application.)


Where in Postgres can I look to see why it's just sitting there?

Thanks

--
Angular momentum makes the world go 'round.

Re: CREATE OR REPLACE FUNCTION statement just sitting there

[Rob Sargent]

On 11/5/18 7:05 PM, Ron wrote:
I've got a very puzzling problem on 9.6.6 systems we just migrated 
from 8.4.  (The same problem happened on 9.6.9, but rolled it back so 
as to make prod have the same version as our Staging systems.)


We've got a giant script full of DROP TRIGGER IF EXISTS and CREATE 
TABLE and DROP TABLE and CREATE OR REPLACE FUNCTION statements.


It's purpose is to drop old parts of partitioned tables and add new 
tables.


It *ALWAYS worked* just fine on our big, ancient, production 8.4 
databases (otherwise I'd have heard the screams of user rage), and on 
our 9.6.6 staging environment.  However, one or more of our big (and 
schema-identical) prod databases (which are each on a different 
server) it is finicky and tends to just "sit" at a random one of the 
CREATE OR REPLACE FUNCTION statements.


The "list all blocking queries" I run doesn't show that anything is 
blocking it (though it blocks everything else), and neither top(1) nor 
iotop(1) show any activity.


If it matters, this script is fed to the databases via the JDBC 
driver, and it works fine when I run it via psql.  (I'd gladly run the 
scripts manually, but these are child databases, and a parent db must 
be updated at the same time by a canned application.)


Where in Postgres can I look to see why it's just sitting there?

Thanks

--
Angular momentum makes the world go 'round.



select * from pg_stat_activity;

might shed some light?

Re: CREATE OR REPLACE FUNCTION statement just sitting there

[Ron]

On 11/05/2018 08:30 PM, Rob Sargent wrote:


On 11/5/18 7:05 PM, Ron wrote:

I've got a very puzzling problem on 9.6.6 systems we just migrated from 
8.4.  (The same problem happened on 9.6.9, but rolled it back so as to 
make prod have the same version as our Staging systems.)


We've got a giant script full of DROP TRIGGER IF EXISTS and CREATE TABLE 
and DROP TABLE and CREATE OR REPLACE FUNCTION statements.


It's purpose is to drop old parts of partitioned tables and add new tables.

It *ALWAYS worked* just fine on our big, ancient, production 8.4 
databases (otherwise I'd have heard the screams of user rage), and on our 
9.6.6 staging environment.  However, one or more of our big (and 
schema-identical) prod databases (which are each on a different server) 
it is finicky and tends to just "sit" at a random one of the CREATE OR 
REPLACE FUNCTION statements.


The "list all blocking queries" I run doesn't show that anything is 
blocking it (though it blocks everything else), and neither top(1) nor 
iotop(1) show any activity.


If it matters, this script is fed to the databases via the JDBC driver, 
and it works fine when I run it via psql.  (I'd gladly run the scripts 
manually, but these are child databases, and a parent db must be updated 
at the same time by a canned application.)


Where in Postgres can I look to see why it's just sitting there?

Thanks

--
Angular momentum makes the world go 'round.



select * from pg_stat_activity;

might shed some light?



That (plus pg_locks)  is the heart of the "list all blocking queries" 
statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring.


--
Angular momentum makes the world go 'round.

Running pg_upgrade Version 11

[rob stone]

Hello,

Trying to run pg_upgrade from version 10 to version 11 on the test
server and pulling the following error:-

could not open version file: /home/postgres/testing/data_v10/PG_VERSION
Failure, exiting

O/S is:- Debian 4.18.10-2 (2018-11-02) x86_64 GNU/Linux

Running pg_upgrade as user postgres.

These are the permissions on the version 10 file:-

-rw--- 1 postgres postgres 3 Mar 23  2018 PG_VERSION

These are the permissions on the version 11 file:-

-rw--- 1 postgres postgres 3 Nov  6 14:50 PG_VERSION

This is the command being run:-

/usr/lib/postgresql/11/bin/pg_upgrade -b /usr/lib/postgresql/10/bin -B
/usr/lib/postgresql/11/bin -d /home/postgres/testing/data_v10 -D
/home/postgres/testing/data_v11


Logged in as user postgres and postgres owns the files created by
initdb, so is this a permissions problem or am I having a brain fade?


TIA,
Rob

Re: Running pg_upgrade Version 11

[Michael Paquier]

On Tue, Nov 06, 2018 at 04:27:35PM +1100, rob stone wrote:
> Logged in as user postgres and postgres owns the files created by
> initdb, so is this a permissions problem or am I having a brain fade?

Having 0600 as umask for those files is normal.  Don't you have more
logs about the error?  You should not see this error, except if data_v10
is not a data folder initialized correctly, so perhaps you messed up
something in your environment?
--
Michael


signature.asc
Description: PGP signature