Re: Restoring database from false update

2020-11-14 Thread David G. Johnston 
On Sunday, November 15, 2020, Maksim Fomin  wrote:

>
> > plsql -d tsvt
> psql (12.5)
> Type "help" for help.
>
> tsvt=# \dt+
> List of relations
> Schema | Name | Type  |  Owner   |  Size   | Description
> +--+---+--+-+-
> public | test | table | postgres | 0 bytes |
> (1 row)
>
> It should have tables 'trade', 'trade4' and some others.
>

What about?

\l+ (Letter “el”)

David J.

Restoring database from false update

2020-11-14 Thread Maksim Fomin 
‐‐‐ Original Message ‐‐‐
On Sunday, November 15, 2020 4:47 AM, David G. Johnston 
 wrote:

> On Fri, Nov 13, 2020 at 1:56 PM Maksim Fomin  wrote:
>
>> Later, I stopped the service and moved backup folder to the usual place. For 
>> some reason psql shows that there are no relations found in the database, 
>> although the database is listed. My next step was to copy data from 
>> file-system level backup (about 4-5 days ago) but the result was the same.
>>
>> How I can restore the database?
>
> Assuming you have a complete and valid v12 data directory backup created from 
> a shutdown server, and containing good WAL files...and that the server is 
> presently running a v12 instance of PostgreSQL you are able to connect to 
> using psql.
>
> What do the following show?
>
> select version();

version
--
PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.0, 64-bit
(1 row)

> show data_directory;

data_directory
---
/home/postgresql/data
(1 row)

ls -alh /home
drwxr-xr-x 1 postgres postgres 8 Nov 13 19:59 postgresql

It points to the data directory I have backed up and restored. According to 
ncdu utility, the data folder has approx. 10.5GiB

> Assuming that the version is 12.x you want to ensure that your data directory 
> backup replaces the entire contents of wherever data_directory is pointing 
> (while the PostgreSQL process is stopped).
>
> Having done that, and starting the server back up, you should find the old 
> cluster to have been restored.
>
> If that doesn't work:

The server starts and I can connect to my database, but there are no tables. I 
start the server with systemctl start postgresql.service:

Nov 15 07:03:41 localhost systemd[1]: Starting PostgreSQL database server...
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.819 GMT [59696] 
LOG: starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, c>
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.821 GMT [59696] 
LOG: listening on IPv6 address "::1", port 5432
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.821 GMT [59696] 
LOG: listening on IPv4 address "127.0.0.1", port 5432
Nov 15 07:03:41 localhost postgres[59696]: 2020-11-15 07:03:41.885 GMT [59696] 
LOG: listening on Unix socket "/run/postgresql/.s.PGSQL>
Nov 15 07:03:42 localhost postgres[59697]: 2020-11-15 07:03:42.139 GMT [59697] 
LOG: database system was shut down at 2020-11-15 07:03:>
Nov 15 07:03:42 localhost postgres[59696]: 2020-11-15 07:03:42.286 GMT [59696] 
LOG: database system is ready to accept connections
Nov 15 07:03:42 localhost systemd[1]: Started PostgreSQL database server.

> plsql -d tsvt
psql (12.5)
Type "help" for help.

tsvt=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
+--+---+--+-+-
public | test | table | postgres | 0 bytes |
(1 row)

It should have tables 'trade', 'trade4' and some others.

> Reviewing log files can help.
>
> If you can report the startup command that is run to launch the postgres 
> process that would help too.
>
> Showing before and after directory structures can help.

I have replaced 'new' data structure from backup, this is the structure of my 
backup and current data:

sudo ls -alh /home/postgresql/data/
total 56K
drwx-- 1 postgres postgres 512 Nov 15 07:03 .
drwxr-xr-x 1 postgres postgres 8 Nov 13 19:59 ..
drwx-- 1 postgres postgres 90 May 24 09:13 base
drwx-- 1 postgres postgres 668 Nov 15 07:04 global
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_commit_ts
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_dynshmem
-rw--- 1 postgres postgres 4.5K Apr 4 2020 pg_hba.conf
-rw--- 1 postgres postgres 1.6K Apr 4 2020 pg_ident.conf
drwx-- 1 postgres postgres 76 Nov 15 07:03 pg_logical
drwx-- 1 postgres postgres 28 Apr 4 2020 pg_multixact
drwx-- 1 postgres postgres 8 Nov 15 07:03 pg_notify
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_replslot
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_serial
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_snapshots
drwx-- 1 postgres postgres 0 Nov 15 07:03 pg_stat
drwx-- 1 postgres postgres 92 Nov 15 07:07 pg_stat_tmp
drwx-- 1 postgres postgres 8 Apr 4 2020 pg_subtrans
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_tblspc
drwx-- 1 postgres postgres 0 Apr 4 2020 pg_twophase
-rw--- 1 postgres postgres 3 Apr 4 2020 PG_VERSION
drwx-- 1 postgres postgres 2.6K Nov 5 06:46 pg_wal
drwx-- 1 postgres postgres 8 Apr 4 2020 pg_xact
-rw--- 1 postgres postgres 88 Apr 4 2020 postgresql.auto.conf
-rw--- 1 postgres postgres 27K Apr 4 2020 postgresql.conf
-rw--- 1 postgres postgres 47 Nov 15 07:03 postmaster.opts
-rw--- 1 postgres postgres 99 Nov 15 07:03 postmaster.pid

Anyway, thanks for reply.

Re: ERROR: could not find tuple for statistics object - is there a way to clean this up?

2020-11-14 Thread Tom Lane 
Morris de Oryx  writes:
> And here's the error that I get back:
> ERROR:  could not find tuple for statistics object 147574.

Can you give a self-contained recipe for triggering this?

regards, tom lane

ERROR: could not find tuple for statistics object - is there a way to clean this up?

2020-11-14 Thread Morris de Oryx 
I've been experimenting with CREATE STATISTICS to declare some functionally
dependent columns. Right now, I'm working with a local copy of Postgres
running on this version:

PostgreSQL 12.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
version 8.1.0 (clang-802.0.42), 64-bit

We deploy on RDS, also in the 12.x line.

Here's an example of the DROP:
DROP TABLE data.samples CASCADE;And

And here's the error that I get back:

ERROR:  could not find tuple for statistics object 147574.

I'm out of my depth here. pg_statistic_ext does not have any row with an
oid or stxrelid of 147574. I've hunted around some in pg_class and
pg_statitic, but can't find any column with a reference to this value.

I tried upgrading (I was on 12.3), shutting down and restarting the server
a few times, and running ANALYZE to see if anything would change. It hasn't.

Any idea how this problem can be created, avoided, or resolved? Many thanks.

For those of you familiar with the source, I've Googled for this error, and
have not seen it discussed. It comes up on pg-hackers about 12 years ago,
and it's located in the source at:

https://doxygen.postgresql.org/objectaddress_8h.html

Here's a snippet where the error is thrown, when HeapTupleIsValid returns
false.

case OCLASS_STATISTIC_EXT:

 {
HeapTuple   stxTup;
Form_pg_statistic_ext stxForm;
char   *nspname;

stxTup = SearchSysCache1(STATEXTOID,
 ObjectIdGetDatum(object->objectId));
if (!HeapTupleIsValid(stxTup))
{
if (!missing_ok)
elog(ERROR, "could not find tuple for statistics object %u",
 object->objectId);
break;
}

stxForm = (Form_pg_statistic_ext) GETSTRUCT(stxTup);

/* Qualify the name if not visible in search path */
if (StatisticsObjIsVisible(object->objectId))
nspname = NULL;
else
nspname = get_namespace_name(stxForm->stxnamespace);

appendStringInfo(, _("statistics object %s"),
 quote_qualified_identifier(nspname,
NameStr(stxForm->stxname)));

ReleaseSysCache(stxTup);
break;
 }

Re: Range partitioning and overlap

2020-11-14 Thread David G. Johnston 
On Fri, Nov 13, 2020 at 2:08 PM Edson Richter 
wrote:

> *De:* Tom Lane 
> *Enviado:* sexta-feira, 13 de novembro de 2020 17:58
> *Para:* Edson Richter 
> *Cc:* David G. Johnston ; pgsql-general <
> pgsql-gene...@postgresql.org>
> *Assunto:* Re: Range partitioning and overlap
>
> Edson Richter  writes:
> > Further on the documentation: "When creating a range partition, the
> lower bound specified with FROM is an inclusive bound, whereas the upper
> bound specified with TO is an exclusive bound."
>
> > I'm pretty sure I cannot find this statement in PostgreSQL 13
> documentation page about partitioning. May be this statement is in another
> page?
>
> It's in the CREATE TABLE reference page.  Seems like it would be a good
> idea to have it also in ddl.sgml's discussion of partitioning, though.
>
>
Patch thread with commit:

https://www.postgresql.org/message-id/dm6pr13mb3988736cf8f5dc5720440231cf...@dm6pr13mb3988.namprd13.prod.outlook.com

David J.

Re: Restoring database from false update

2020-11-14 Thread David G. Johnston 
On Fri, Nov 13, 2020 at 1:56 PM Maksim Fomin  wrote:

> Later, I stopped the service and moved backup folder to the usual place.
> For some reason psql shows that there are no relations found in the
> database, although the database is listed. My next step was to copy data
> from file-system level backup (about 4-5 days ago) but the result was the
> same.
>
> How I can restore the database?
>

Assuming you have a complete and valid v12 data directory backup created
from a shutdown server, and containing good WAL files...and that the server
is presently running a v12 instance of PostgreSQL you are able to connect
to using psql.

What do the following show?

select version();
show data_directory;

Assuming that the version is 12.x you want to ensure that your data
directory backup replaces the entire contents of wherever data_directory is
pointing (while the PostgreSQL process is stopped).

Having done that, and starting the server back up, you should find the old
cluster to have been restored.

If that doesn't work:

Reviewing log files can help.

If you can report the startup command that is run to launch the postgres
process that would help too.

Showing before and after directory structures can help.

David J.

Re: New "function tables" in V13 documentation

2020-11-14 Thread David G. Johnston 
On Fri, Nov 13, 2020 at 1:48 PM Adrian Klaver 
wrote:

> Which is an indication that for changes of this scope it would be
> prudent to create a mock up and have end users see and comment on before
> rolling them out.
>

There were mockups and people did provide comments.  Do you have any
concrete suggestions on what should have been done, or done differently?

David J.

Re: I have just downloaded Postgre SQL and "pgadmin 4" doesn't open.

2020-11-14 Thread Adrian Klaver 

On 11/14/20 3:45 PM, Ecenur Corlu wrote:
*I have just downloaded the latest version of PostgreSQL (vs.13.1) and 


Where did you download it from?

More comments inline below.

when I try to open "pgadmin 4", it doesn't open.* It first shows 


How are you starting it?


*/windows wait cursor/* and then the wait cursor gets lost. Nothing happens.

*I also realized that there is no folder created under* C:\Program 
Files. *like this* >>>"C:\Program Files\pgAdmin 4" I can't find log docs.


I have also tried these methods.

 1. I have deleted PostgreSQL and re-downloaded it. It didn't make any
difference. The same mistake is happening again.
 2. I have downloaded Java.


pgAdmin uses Python/JS/CSS/HTML. Java is not a requirement.


 3. I have looked at "HKEY_CLASSES_ROOT.css\Content Type" to see if
"text/css" is written there. And it was in "text/css" format, so
there wasn't any mistake there.
 4. I deleted "Anaconda" which was downloaded on my computer. I have
read that it blocks pgadmin 4 to start.
 5. I made Chrome my default browser.
 6. I added in "HKEY_CLASSES_ROOT.jz" a value name: "Content Type" in
"Reg SZ" type and it's data is "text/javascript" .Previously there
was just"default" value name and it's data was "JSFile" Ihaven't
deleted this yet.


Thank you in advance for your help.




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

I have just downloaded Postgre SQL and "pgadmin 4" doesn't open.

2020-11-14 Thread Ecenur Corlu 
*I have just downloaded the latest version of PostgreSQL (vs.13.1) and when
I try to open "pgadmin 4", it doesn't open.* It first shows *windows wait
cursor* and then the wait cursor gets lost. Nothing happens.

*I also realized that there is no folder created under* C:\Program Files. *like
this* >>>"C:\Program Files\pgAdmin 4" I can't find log docs.

I have also tried these methods.

   1. I have deleted PostgreSQL and re-downloaded it. It didn't make any
   difference. The same mistake is happening again.
   2. I have downloaded Java.
   3. I have looked at "HKEY_CLASSES_ROOT.css\Content Type" to see if
   "text/css" is written there. And it was in "text/css" format, so there
   wasn't any mistake there.
   4. I deleted "Anaconda" which was downloaded on my computer. I have read
   that it blocks pgadmin 4 to start.
   5. I made Chrome my default browser.
   6. I added in "HKEY_CLASSES_ROOT.jz" a value name: "Content Type" in
   "Reg SZ" type and it's data is "text/javascript" .Previously there was
   just "default" value name and it's data was "JSFile" I haven't deleted
   this yet.


Thank you in advance for your help.

Re: conflict with recovery when delay is gone

2020-11-14 Thread Mohamed Wael Khobalatte 
On Sat, Nov 14, 2020 at 2:46 PM Radoslav Nedyalkov 
wrote:

>
>
> On Fri, Nov 13, 2020 at 8:13 PM Radoslav Nedyalkov 
> wrote:
>
>>
>>
>> On Fri, Nov 13, 2020 at 7:37 PM Laurenz Albe 
>> wrote:
>>
>>> On Fri, 2020-11-13 at 15:24 +0200, Radoslav Nedyalkov wrote:
>>> > On a very busy master-standby setup which runs typical olap processing
>>> -
>>> > long living , massive writes statements,  we're getting on the standby:
>>> >
>>> >  ERROR:  canceling statement due to conflict with recovery
>>> >  FATAL:  terminating connection due to conflict with recovery
>>> >
>>> > The weird thing is that cancellations happen usually after standby has
>>> experienced
>>> > some huge delay(2h), still not at the allowed maximum(3h). Even
>>> recently run statements
>>> > got cancelled when the delay is already at zero.
>>> >
>>> > Sometimes the situation got relaxed after an hour or so.
>>> > Restarting the server instantly helps.
>>> >
>>> > It is pg11.8, centos7, hugepages, shared_buffers 196G from 748G.
>>> >
>>> > What phenomenon could we be facing?
>>>
>>> Hard to say.  Perhaps an unusual kind of replication conflict?
>>>
>>> What is in "pg_stat_database_conflicts" on the standby server?
>>>
>>
>> db01=# select * from pg_stat_database_conflicts;
>>  datid |  datname  | confl_tablespace | confl_lock | confl_snapshot |
>> confl_bufferpin | confl_deadlock
>>
>> ---+---+--+++-+
>>  13877 | template0 |0 |  0 |  0 |
>>   0 |  0
>>  16400 | template1 |0 |  0 |  0 |
>>   0 |  0
>>  16402 | postgres  |0 |  0 |  0 |
>>   0 |  0
>>  16401 | db01  |0 |  0 | 51 |
>>   0 |  0
>> (4 rows)
>>
>> On a freshly restarted standby we've just got similar behaviour after a 2
>> hours delay and a slow catch-up.
>> confl_snapshots is 51 and we have exactly the same number cancelled
>> statements.
>>
>>
> No luck so far. Searching for the explanation i found we fail into the
> unexplained case when
> snapshot conflicts happen even hot_standby_feedback is on.
>
> Thanks,
> Rado
>
>

Perhaps you have a value set for old_snapshot_threshold? If not, do the
walreceiver connections drop out?

Re: conflict with recovery when delay is gone

2020-11-14 Thread Radoslav Nedyalkov 
On Fri, Nov 13, 2020 at 8:13 PM Radoslav Nedyalkov 
wrote:

>
>
> On Fri, Nov 13, 2020 at 7:37 PM Laurenz Albe 
> wrote:
>
>> On Fri, 2020-11-13 at 15:24 +0200, Radoslav Nedyalkov wrote:
>> > On a very busy master-standby setup which runs typical olap processing -
>> > long living , massive writes statements,  we're getting on the standby:
>> >
>> >  ERROR:  canceling statement due to conflict with recovery
>> >  FATAL:  terminating connection due to conflict with recovery
>> >
>> > The weird thing is that cancellations happen usually after standby has
>> experienced
>> > some huge delay(2h), still not at the allowed maximum(3h). Even
>> recently run statements
>> > got cancelled when the delay is already at zero.
>> >
>> > Sometimes the situation got relaxed after an hour or so.
>> > Restarting the server instantly helps.
>> >
>> > It is pg11.8, centos7, hugepages, shared_buffers 196G from 748G.
>> >
>> > What phenomenon could we be facing?
>>
>> Hard to say.  Perhaps an unusual kind of replication conflict?
>>
>> What is in "pg_stat_database_conflicts" on the standby server?
>>
>
> db01=# select * from pg_stat_database_conflicts;
>  datid |  datname  | confl_tablespace | confl_lock | confl_snapshot |
> confl_bufferpin | confl_deadlock
>
> ---+---+--+++-+
>  13877 | template0 |0 |  0 |  0 |
>   0 |  0
>  16400 | template1 |0 |  0 |  0 |
>   0 |  0
>  16402 | postgres  |0 |  0 |  0 |
>   0 |  0
>  16401 | db01  |0 |  0 | 51 |
>   0 |  0
> (4 rows)
>
> On a freshly restarted standby we've just got similar behaviour after a 2
> hours delay and a slow catch-up.
> confl_snapshots is 51 and we have exactly the same number cancelled
> statements.
>
>
No luck so far. Searching for the explanation i found we fail into the
unexplained case when
snapshot conflicts happen even hot_standby_feedback is on.

Thanks,
Rado

Re: PostgreSQL equivalent to Oracles ANYDATASET

2020-11-14 Thread Dirk Mika 
Hi,

many thanks for this suggestion. But the problem with this is that you have to 
know which columns are returned when you call the function.

Regards
Dirk

--
Dirk Mika
Software Developer



[cid:mt_c1c59b3d-dd43-4ca6-992b-79f5a19f5999.png]

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany



fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de



AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
Von: Christoph Moench-Tegeder 
Datum: Freitag, 13. November 2020 um 18:23
An: Dirk Mika 
Cc: "pgsql-general@lists.postgresql.org" 
Betreff: Re: PostgreSQL equivalent to Oracles ANYDATASET

## Dirk Mika (dirk.m...@mikatiming.de):

SELECT * FROM TABLE(series_pkg.get_results(1));
The purpose of this function is to provide a DATASET, which has
different columns in the result depending on the passed parameter.
Is there any way to achieve something similar in PostreSQL?

testing=# CREATE OR REPLACE FUNCTION public.rr(p INTEGER)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $function$
BEGIN
  IF p = 1 THEN
RETURN NEXT ('k1'::TEXT, 'v1'::TEXT);
RETURN NEXT ('k2'::TEXT, 'v2'::TEXT);
  ELSE
RETURN NEXT (23::INTEGER, 42::INTEGER, 'abc'::TEXT);
RETURN NEXT (42::INTEGER, 23::INTEGER, 'xyz'::TEXT);
  END IF;
  RETURN;
END;
$function$;
CREATE FUNCTION

testing=# SELECT * FROM rr(2) f(a INTEGER, b INTEGER, c TEXT);
a  | b  |  c
++-
23 | 42 | abc
42 | 23 | xyz
(2 rows)

testing=# SELECT * FROM rr(1) f(x TEXT, y TEXT);
x  | y
+
k1 | v1
k2 | v2
(2 rows)

Regards,
Christoph

--
Spare Space