[GENERAL] Restore LargeObjects on different server

[Durumdara]

Dear Members!

Because of upload/download progress we used LargeObjects to store some
files in one of our database (and not bytea).

Only this database uses the OID-s of these files.

In the near future we must move to another server.
This new server is also working now, the moving of databases is continous,
one by one.

The main problem that LargeObjects are stored in the system table(s). Same
OIDs could links to different LargeObjects.

The old and new PGSQL server may have same OID values (160606 f. e.) with
different content (LargeObject).
In old this is one of our file data, in the new this is a table's system
definition.

Can we backup this database WITH OIDs, and restore it in new server without
worrying of LargeObject overwriting?

Or how to migrate (move) this database with largeobjects in new to preserve
the consistency of copied database and lobs, but preserve the existing
OID/largeobject pairs in new server?

Thank you for the info/help!

Best regards
   dd

Re: [GENERAL] Restore LargeObjects on different server

[Durumdara]

Hi!

Somebody wrote me that:

The pg_catalog schema is system schema, but it is IN the DB.

Is this true? So OID is not global (out from DB)?

So we can dump and restore the DB with OIDs without collision in new server?

Thank you!

dd

2017-10-12 11:35 GMT+02:00 Durumdara :

> Dear Members!
>
> Because of upload/download progress we used LargeObjects to store some
> files in one of our database (and not bytea).
>
> Only this database uses the OID-s of these files.
>
> In the near future we must move to another server.
> This new server is also working now, the moving of databases is continous,
> one by one.
>
> The main problem that LargeObjects are stored in the system table(s). Same
> OIDs could links to different LargeObjects.
>
> The old and new PGSQL server may have same OID values (160606 f. e.) with
> different content (LargeObject).
> In old this is one of our file data, in the new this is a table's system
> definition.
>
> Can we backup this database WITH OIDs, and restore it in new server
> without worrying of LargeObject overwriting?
>
> Or how to migrate (move) this database with largeobjects in new to
> preserve the consistency of copied database and lobs, but preserve the
> existing OID/largeobject pairs in new server?
>
> Thank you for the info/help!
>
> Best regards
>dd
>
>
>
>

Re: [GENERAL] Restore LargeObjects on different server

[Laurenz Albe]

Durumdara wrote:
> > Because of upload/download progress we used LargeObjects to store some 
> > files in one of our database (and not bytea).
> > Only this database uses the OID-s of these files.
> > 
> > In the near future we must move to another server.
> > This new server is also working now, the moving of databases is continous, 
> > one by one.
> > 
> > The main problem that LargeObjects are stored in the system table(s). Same 
> > OIDs could links to different LargeObjects.
> > 
> > The old and new PGSQL server may have same OID values (160606 f. e.) with 
> > different content (LargeObject).
> > In old this is one of our file data, in the new this is a table's system 
> > definition.
> > 
> > Can we backup this database WITH OIDs, and restore it in new server without 
> > worrying of LargeObject overwriting?
> > 
> > Or how to migrate (move) this database with largeobjects in new to preserve 
> > the consistency of copied database and lobs, but preserve the existing 
> > OID/largeobject pairs in new server?
> 
> Somebody wrote me that:
> 
> The pg_catalog schema is system schema, but it is IN the DB.
> 
> Is this true? So OID is not global (out from DB)?
> 
> So we can dump and restore the DB with OIDs without collision in new server?

OIDs are assigned from a database-wide counter so that there can be no collision
within one database.  But there is nothing that prevents OID collision between
different databases.

pg_dump dumps large objects with their OID, so they will have the same
OID when they are restored in another database.

This will lead to a collision if there are already large objects with the same 
OID
in the second database.

I'd restore the large objects and manually fix all collisions
(import the problematic large objects with a different OID and adjust
the referencing tables accordingly).

This might prove difficult if there are a lot of collisions, but I don't think
that there is a better way.

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Fwd: [BUGS] BUG #14850: Implement optinal additinal parameter for 'justify' date/time function

[KES]

 Пересылаемое сообщение11.10.2017, 17:12, "Pavel Stehule" :Hi2017-10-11 12:35 GMT+02:00  :The following bug has been logged on the website:

Bug reference:      14850
Logged by:          Eugen Konkov
Email address:      kes-...@yandex.ru
PostgreSQL version: 10.0
Operating system:   Linux mint 18: Linux work 4.4.0-57-generic #78-Ubu
Description:

Hi. I try to do next math:

select extract( month from justify_days( timestamp '2016-01-31' +interval '1
month' -timestamp '2016-01-31') );
 date_part
---
         0
(1 row)

I expect `1` but get `0`. But here everything is right:

>Adjust interval so 30-day time periods are represented as months

https://www.postgresql.org/docs/9.6/static/functions-datetime.html

But with ability to setup justify date the math will be more sharp.

Please implement next feature:

select extract( month from justify_days( timestamp '2016-01-31' +interval '1
month' -timestamp '2016-01-31'), timestamp '2016-01-31' );
 date_part
---
         1
(1 row)

This is useful when I try to calculate how much month are left between
service start and end dates.This is not the bug, so pgsql-hackers, pgsql-general are better places for this discussion I am thinking so your request has sense, and should be registered in ToDo list https://wiki.postgresql.org/wiki/TodoYou can try to connect people from PostgreSQL Pro company for implementation.RegardsPavel

Thank you.


--
Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

 Конец пересылаемого сообщения 

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

[Scott Marlowe]

On Tue, Oct 10, 2017 at 4:28 PM, pinker  wrote:
>
> Yes, it would be much easier if it would be just single query from the top,
> but the most cpu is eaten by the system itself and I'm not sure why.

You are experiencing a context switch storm. The OS is spending so
much time trying to switch between 1,000+ processes it doesn't have
any time left to do much else.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: [BUGS] BUG #14850: Implement optinal additinal parameter for 'justify' date/time function

[Brian Dunavant]

A 'month' is an abstract measurement of time.  Sometimes it's 29 days, 30,
or 31.   You cannot say "I have 30 days, how many months is that?" because
the answer is "it depends".

 -  gives you an interval in days.   In your example, you took
Jan 31 2016 and added "1 month".  Postgres says "I know feb 2016 is 29
days" and did it automatically for you.   When you then subtracted Jan 31
2016, you now have "29 days".   Postgres can no longer say "that is 1
month" because you cannot go that direction.

You are also using extract(month from X) incorrectly if you want the number
of months between any time period.   That will only return a value between
0 and 11.

It will also be difficult because you are starting from a random day in the
month, making it hard to really know what you mean.  Postgres' age()
function may be able to help you with 'months'.

flpg=# select age( '2016-02-01'::timestamp, '2016-01-01'::timestamp );
  age
---
 1 mon

flpg=# select age( '2016-02-29'::timestamp, '2016-01-31'::timestamp );
   age
-
 29 days
(1 row)

flpg=# select age( '2016-03-01'::timestamp, '2016-01-31'::timestamp );
 age
-
 1 mon 1 day





On Thu, Oct 12, 2017 at 4:00 AM, KES  wrote:

>
>
>  Пересылаемое сообщение
> 11.10.2017, 17:12, "Pavel Stehule" :
>
> Hi
>
> 2017-10-11 12:35 GMT+02:00 :
>
> The following bug has been logged on the website:
>
> Bug reference:  14850
> Logged by:  Eugen Konkov
> Email address:  kes-...@yandex.ru
> PostgreSQL version: 10.0
> Operating system:   Linux mint 18: Linux work 4.4.0-57-generic #78-Ubu
> Description:
>
> Hi. I try to do next math:
>
> select extract( month from justify_days( timestamp '2016-01-31' +interval
> '1
> month' -timestamp '2016-01-31') );
>  date_part
> ---
>  0
> (1 row)
>
> I expect `1` but get `0`. But here everything is right:
>
> >Adjust interval so 30-day time periods are represented as months
>
> https://www.postgresql.org/docs/9.6/static/functions-datetime.html
>
> But with ability to setup justify date the math will be more sharp.
>
> Please implement next feature:
>
> select extract( month from justify_days( timestamp '2016-01-31' +interval
> '1
> month' -timestamp '2016-01-31'), timestamp '2016-01-31' );
>  date_part
> ---
>  1
> (1 row)
>
> This is useful when I try to calculate how much month are left between
> service start and end dates.
>
>
> This is not the bug, so pgsql-hackers, pgsql-general are better places for
> this discussion
>
> I am thinking so your request has sense, and should be registered in ToDo
> list https://wiki.postgresql.org/wiki/Todo
>
> You can try to connect people from PostgreSQL Pro company for
> implementation.
>
> Regards
>
> Pavel
>
>
> Thank you.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-b...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>
>
>  Конец пересылаемого сообщения 
>

[GENERAL] EAV Designs for Multi-Tenant Applications

[Nic Pottier]

Howdy all,

We maintain a hosted multi-tenant system for a large number of users. Each
user has what we call an "organization" or "org" which is their sandbox. In
that organization they can manage contacts and define custom fields on
those contacts, which then have values per contact.

We have thousands of organizations, some organizations have hundreds of
custom fields. Some organizations have millions of contacts. We have tens
of millions of values across organizations, so relatively sparse with a
large number of tiny / unused organizations. All these numbers will
continue to grow, though single organizations greater than 10 million
contacts is unlikely.

What is the recommended way of modeling this in order to allow performant
queries on the custom fields?

Right now we basically use an EAV model (a single contact_fields table)
with compound indexes that join the field key and field value so as to
force locality in the index. That has worked ok, but occasionally falls on
its face when the query planner makes a wrong guess due to the (obviously
skewed) statistics it has to work with. Multi-field queries can also get
painful, especially on the largest organizations with millions of contacts.

What other approaches should we be looking at? We've brainstormed different
approaches but would love some wisdom to help us narrow down what are
reasonable things to try testing out. Most of our designs hone in on
creating a table per organizations to hold field values and dynamically
creating indexes on that. The idea being that we won't kill our insert
performance as much by only having one index per field to check on inserts
and table statistics should still be ok. (how are statistics managed for
JSONB fields?) The main question we have is what is going to happen if we
have thousands (or tens of thousands) of tables on a single database? The
good news is the vast majority of our organizations are idle at any point
in time.

Approaches we've thought about and questions / pros / cons:

1) Add a JSONB field on our (shared across organizations) contact table,
store field values there. Create JSONB indexes per unique field.
   pros: nice having the locality of data on contact, multi field queries
are likely way better, query performance should be good
   cons: we have to create thousands of indexes? we have to use uuids as
keys to keep our indexes org-specific? insert performance suffers from
having thousands of partial indexes (how badly)?

2) Create a table per organization `contact_fields_[org_id]` containing a
column per field. Create columns and indexes per unique field.
   pros: locality is nice again, multi field queries are better, query and
insert performance should be good.
   cons: thousands of tables with up to 100 indexes per, is that going to
blow up?

3) Create a table per organization `contacts_fields_[org_id]` which
contains a `fields` JSONB column, Create JSONB indexes per unique field.
  pros: locality is good, multi field queries good, query performance
should be good. Adding and removing fields is a bit simpler than 2) case
above and naming can be a bit clearer than 1) as we don't have to worry
about multi-org key name collisions
  cons: same as 2) but with a JSONB flavor, hundreds of JSONB indexes on
thousands of tables, thousands of tables

4) Create a database per organization? Use 1) above
  pros: all data is localized, might see performance improvements
elsewhere, query and insert performance should be good
  cons: mother of a refactor :)  what happens with thousands of databases
on a single box? is this actually better from the perspective of getting to
offload currently inactive orgs?

What other approaches should we be considering? I know EAV is a pain,
especially in multi-tenant situations, but we'd love to hear success (and
failure) stories from the community on how they've dealt with these.

Cheers,

-Nic

[GENERAL] Index corruption & broken clog

[Benoit Lobréau]

Hi,

One of my PostgreSQL server crashed badly yesterday. A process was killed
(see dmesg below) and postgres was stuck with theses process:

postgres  2083 1  0 Oct08 ?00:19:02
/usr/lib/postgresql/9.5/bin/postgres -D /home/postgres/data/i090/systeme
postgres  2221  2083  0 Oct08 ?00:02:03 postgres: i090: logger
process
postgres 14068  2083  8 Oct09 ?02:50:22 [postgres]
postgres 19668 19665  0 09:40 pts/000:00:00 -su

I couldn't stop it with "pg_ctl stop immediate". We had to kill them and
release the shared memory block.

dmesg showed this.

[Tue Oct 10 07:45:29 2017] postgres[25506]: segfault at 7f2253ecb000 ip
7f225aead994 sp 7ffc2a9c0c28 error 4 in libc-2.23.so
[7f225ae0e000+1c]
[Tue Oct 10 07:45:30 2017] BUG: Bad rss-counter state mm:8800516a7c00
idx:0 val:178
[Tue Oct 10 07:45:31 2017] BUG: Bad page map in process postgres
pte:480090248c8d pmd:2c3de067

When I checked the postgresql log I found lots of these :

 could not read block 76638 in file
"pg_tblspc/16395/PG_9.5_201510051/16396/20082": read only 0 of 8192 bytes
 could not read block 76669 in file
"pg_tblspc/16395/PG_9.5_201510051/16396/19993": read only 0 of 8192 bytes
 index "degrade_pkey" contains corrupted page at block 60392
 index "degrade_pkey" contains unexpected zero page at block 60392
 index "idx_coor_brute_geometrie" contains corrupted page at block 53061
 index "idx_coor_brute_geometrie" contains corrupted page at block 56109
 index "idx_coor_brute_geometrie" contains corrupted page at block 58867
 index "idx_coor_brute_geometrie" contains corrupted page at block 59003
 index "idx_coor_brute_geometrie" contains corrupted page at block 60138
 index "idx_coor_brute_geometrie" contains corrupted page at block 71974
 index "idx_coor_brute_geometrie" contains corrupted page at block 80968
 index "idx_coor_brute_geometrie" contains corrupted page at block 86777
 index "idx_coor_brute_geometrie" contains unexpected zero page at block
78586
 index "idx_coor_proj_ligne_geometrie" contains corrupted page at block
64349
 index "idx_numero_course" contains corrupted page at block 3435
 index "idx_numero_course" contains corrupted page at block 7176
 index "idx_numero_engin" contains corrupted page at block 30996
 index "idx_utc_envoi_composant_amont" contains corrupted page at block
76497
 index "idx_utc_envoi_reseau" contains corrupted page at block 76524
 right sibling's left-link doesn't match: block 102923 links to 49947
instead of expected 1 in index "idx_utc_reception_fournisseur"
 right sibling's left-link doesn't match: block 103014 links to 51087
instead of expected 1 in index "idx_utc_reception_composant_amont"
 right sibling's left-link doesn't match: block 76978 links to 59148
instead of expected 1 in index "idx_utc_envoi_reseau"
 right sibling's left-link doesn't match: block 77073 links to 82204
instead of expected 1 in index "idx_utc_envoi_composant_amont"

The errors started to show up on inserts 8 hours before the crash.
I dont understand because when I look into theses pages with pageinspect
they are not empty / dont look bad.
For exemple, this  "SELECT * FROM bt_page_items('degrade_pkey',60392);"
showed a normal page but the error messages claims its zeroed.

I checked the tables for duplicates on the primary key. Checksum are
enabled and I didn't get any error message when I scanned the tables.
I found 77 duplicates on one table and this:

ERROR:  could not access status of transaction 3443523584
DETAIL:  Could not open file "pg_clog/0CD4": No such file or directory.

I dont think this transaction ever existed given the files I found in the
clog directory.

postgres@ulbdgomp01:~/data/igomp090/systeme/pg_clog$ ls -al
total 30292
drwx--  2 postgres postgres   4096 Oct  9 22:50 .
drwx-- 18 postgres postgres   4096 Oct 12 02:32 ..
-rw---  1 postgres postgres 262144 Aug 16 22:50 
-rw---  1 postgres postgres 262144 Aug 17 16:20 0001
-rw---  1 postgres postgres 262144 Aug 17 22:25 0002
...
-rw---  1 postgres postgres 262144 Oct  7 19:31 0072
-rw---  1 postgres postgres 262144 Oct  9 22:50 0073
-rw---  1 postgres postgres 122880 Oct 12 11:56 0074

In your opinion :
* am I missing a (lots of) clog file(s) or is it a error in the heap page
giving an erroneous txid ?
* Is there a way to find which row has the transaction number 3443523584
(in t_xmin t_xmax I suppose)?
* Why am I not seeing any obiously broken page when I check with
pageinspect ?

We are using:
* Ubuntu 16.04 LTS
* PotgreSQL 9.5.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

Thx a lot.

Benoit.

[GENERAL] REASSIGN OWNED simply doesn't work

[Sam Gendler]

psql 9.6.3 on OS X.

I'm dealing with a production database in which all db access has been made
by the same user - the db owner, which isn't actually a superuser because
the db runs on amazon RDS - amazon retains the superuser privilege for its
own users and makes non-superuser role with createrole and createdb
privileges for use as the primary role by the AWS account.

I am now tasked with securing the db, which means I want to create a role
with reduced permissions that I can transfer ownership to, and then a bunch
of roles for various types of access - developer ad-hoc access,
application-specific roles, etc.

My first task was to simply create a role without createdb and createrole
privilege which can be the owner of everything.

The original role was called 'stemadmin' and I have created a role called
'stem'

stem_local=> \du

List of roles

  Role name  | Attributes |
  Member of

-+--
--+--

 sgendler| Superuser, Create role, Create DB, Replication, Bypass RLS |
{}

 stem| No inheritance, Create role|
{}

 stemadmin   | No inheritance, Create role, Create DB |
{stem}

 stemdropper | No inheritance, Create role, Create DB |
{stemadmin,stem}

I have a superuser called sgendler, but I cannot use it, because I do not
have superuser access in my production environment (RDS).  Every object in
the database is owned by stemadmin.

If I login to the database as stemadmin and attempt to execute 'REASSIGN
OWNED BY stemadmin TO stem;' I receive the following requiring superuser or
not being able to be the owning role directly when reassigning.  This seems
like a documentation oversight.

stem_local=> reassign owned by stemadmin to stem;
ERROR:  permission denied to reassign objects

So it won't allow me to give away my own permissions.  Obviously, I can't
execute that statement as 'stem' since that would be stealing permissions.
So my only remaining option was to create the 'stemdropper' role, which is
a member of both 'stemadmin' and 'stem' so it should have permissions on
objects owned by both stem and stemadmin.  Yet when I run the same
statement as 'stemdropper' I still get the permission denied message.  So
how am I supposed to reassign anything if I cannot become superuser?  Do I
really have to dump the entire db without ownership info, then reimport it
into a new db as the new owner?  That seems like a ridiculously slow and
ineffective way to accomplish that.  And the error message is tremendously
unhelpful, all things considered.

It should be noted that if I alter all 3 roles with 'inherit' it still
doesn't work.  It would appear that the only way to 'reassign owned' is as
a superuser (which is contradicted by the documentation), which isn't
accessible in RDS.

Meanwhile, the documentation merely says something about needing to have
permissions to access both roles in a reassign command, but says nothing
about
And while I have you, the 'new' page for subscribing to mailing lists just
throws an error.  It took me way too long to become a member of this list
because the instructions specifically sent me to an ineffective method.
Not exactly new-user friendly.  I've been using postgresql for more than a
decade and have been a member of various lists for that long, but not this
one.  Were I new to the postgresql ecosystem, I'd have probably quit in
frustration when I couldn't log in after creating an account just to get on
a mailing list so I can send an email.

Re: [GENERAL] REASSIGN OWNED simply doesn't work

[Stephen Cook]

On 2017-10-12 21:09, Sam Gendler wrote:
> psql 9.6.3 on OS X.
> 
> I'm dealing with a production database in which all db access has been
> made by the same user - the db owner, which isn't actually a superuser
> because the db runs on amazon RDS - amazon retains the superuser
> privilege for its own users and makes non-superuser role with createrole
> and createdb privileges for use as the primary role by the AWS account.
> 
> I am now tasked with securing the db, which means I want to create a
> role with reduced permissions that I can transfer ownership to, and then
> a bunch of roles for various types of access - developer ad-hoc access,
> application-specific roles, etc.
> 
> My first task was to simply create a role without createdb and
> createrole privilege which can be the owner of everything.
> 
> The original role was called 'stemadmin' and I have created a role
> called 'stem'
> 
> stem_local=> \du
> 
>                                         List of roles
> 
>   Role name  |                         Attributes                      
>   |    Member of     
> 
> -++--
> 
>  sgendler    | Superuser, Create role, Create DB, Replication, Bypass
> RLS | {}
> 
>  stem        | No inheritance, Create role                             
>   | {}
> 
>  stemadmin   | No inheritance, Create role, Create DB                  
>   | {stem}
> 
>  stemdropper | No inheritance, Create role, Create DB                  
>   | {stemadmin,stem}
> 
> 
> I have a superuser called sgendler, but I cannot use it, because I do
> not have superuser access in my production environment (RDS).  Every
> object in the database is owned by stemadmin.
> 
> If I login to the database as stemadmin and attempt to execute 'REASSIGN
> OWNED BY stemadmin TO stem;' I receive the following requiring superuser
> or not being able to be the owning role directly when reassigning.  This
> seems like a documentation oversight.
> 
> stem_local=> reassign owned by stemadmin to stem;
> ERROR:  permission denied to reassign objects
> 
> So it won't allow me to give away my own permissions.  Obviously, I
> can't execute that statement as 'stem' since that would be stealing
> permissions. So my only remaining option was to create the 'stemdropper'
> role, which is a member of both 'stemadmin' and 'stem' so it should have
> permissions on objects owned by both stem and stemadmin.  Yet when I run
> the same statement as 'stemdropper' I still get the permission denied
> message.  So how am I supposed to reassign anything if I cannot become
> superuser?  Do I really have to dump the entire db without ownership
> info, then reimport it into a new db as the new owner?  That seems like
> a ridiculously slow and ineffective way to accomplish that.  And the
> error message is tremendously unhelpful, all things considered. 
> 
> It should be noted that if I alter all 3 roles with 'inherit' it still
> doesn't work.  It would appear that the only way to 'reassign owned' is
> as a superuser (which is contradicted by the documentation), which isn't
> accessible in RDS.
> 
> Meanwhile, the documentation merely says something about needing to have
> permissions to access both roles in a reassign command, but says nothing
> about 
> And while I have you, the 'new' page for subscribing to mailing lists
> just throws an error.  It took me way too long to become a member of
> this list because the instructions specifically sent me to an
> ineffective method.  Not exactly new-user friendly.  I've been using
> postgresql for more than a decade and have been a member of various
> lists for that long, but not this one.  Were I new to the postgresql
> ecosystem, I'd have probably quit in frustration when I couldn't log in
> after creating an account just to get on a mailing list so I can send an
> email.


One of the several reasons I do not like RDS...

I have not been able to figure it out either. Instead I used the
information_schema to generate a bunch of 'ALTER xxx OWNER TO yyy;'
statements, which when run as your current owner user will allow you to
give away your ownership to another user.

After that, make sure to only create objects using the "stepmadmin"
user, or you'll have to jump through hoops yet again.


-- Stephen





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general