Re: Could not open file pg_xact/0E97
Nope. It's an intensive OLAP data-warehouse with queries hanging in waits for hours. >From the vacuum full error and commit file which is gone due to the supposedly moved age mark, It looks more like a bug and not an IO error. Rado On Tue, Jul 21, 2020 at 2:38 AM Jeremy Schneider wrote: > > On Jul 18, 2020, at 14:18, Radoslav Nedyalkov > wrote: > > > Well. the vacuum full failed with > > vacuumdb: vacuuming of table "olap.transactions_and_fees_2020_01" in > database "db" failed: ERROR: found xmin 3916900817 from before > relfrozenxid 80319533 > >> > Do you have checksums enabled for this database? > > -Jeremy > > Sent from my TI-83 > >
Re: Logical replication from 11.x to 12.x and "unique key violations"
Thomas Kellerer writes: > Tom Lane schrieb am 20.07.2020 um 20:04: >> Yeah, duplicate keys does seem odd here. Can you provide a self >> contained example? > I'll try, but this is a production system. > Extracting the necessary anonymous data will be tricky. If this is a PG bug, it should be possible to reproduce it with completely random/generated data. The key ingredient that you have and the rest of us don't is the process and timing by which the primary key values are introduced. > Is there any chance the version difference might cause this? > And a slightly outdated 11.x at that? Hmmm ... I do not recall any recent bug fixes that seem to match this symptom, but replication isn't a part of the code that I'm the world's best expert on. In any case, we do offer as standard advice that you should reproduce a problem on the latest minor release before filing a bug report. regards, tom lane
Re: Logical replication from 11.x to 12.x and "unique key violations"
Tom Lane schrieb am 21.07.2020 um 09:39: > In any case, we do offer as standard advice that you should reproduce > a problem on the latest minor release before filing a bug report. I know ;) I already told the "powers to be" and it's being addressed (I also went through the 11.x release notes, but nothing jumped out) I will try if I can create a stand-alone test, but I suspect some timing problem which will be hard to replicate (no pun intended)
Re: How to restore a dump containing CASTs into a database with a new user?
Guten Tag Christophe Pettus, am Montag, 20. Juli 2020 um 19:21 schrieben Sie: > On a database with multiple users, you can't just get away > with changing the ownership of the types; you have to make sure that > the USAGE is granted appropriately to other users. If a user specifies "--no-owner" and "--role=...", it's pretty clear that the user wants things to be reowned. And that use case could be supported automatically. If other use cases need additional manual work that would be fine, but this concrete one does not in theory. > * A database has user-defined objects in it that only a superuser can create, > and, > * The rest of the database objects are owned by that superuser, and, > * You want to change the ownership of the database objects that can be > changed, and, > * You want to have a single backup that you can restore multiple > times, changing the ownership in a different way each time, and, > * You want to use pg_restore to do it. And pg_restore does all that already, it only misses the special case of CASTs. > This would require a fair amount of surgery to pg_restore. Right > now, pg_restore doesn't really have a "remap these users" > functionality. It's not necessarily about remapping users in general in theory, but instead something like recognizing that creating a CAST first needs to make associated types reowned as well. This could be a fallback strategy like trying to create the CAST, recognizing that it fails because of wrong ownership of types and simply reown the types to the current role. Or creating the CAST itself could be changed as well to optionally do that. In setups like mine with one user per database it's absolutely safe and totally makes sense to reown types for an individual DB, otherwise creating the CAST fails anyway. But obviously I want that CAST, so would do it manually, which is unnecessary in theory and which one could tell the statement with some additional flag or else. Even in cases with multiple different users per DB reowning types make sense, because one has the problem when creating the CAST anyway. So either there's some user designed to create the CAST with, which by definition needs to own the associated type anyway. Or it is done as superuser in which case Postgres could simply not reown because it's not necessary. Depends on if even different superusers need to own types or not. > --no-owner *looks* like it does that, and can be > used for that in certain cases, but the user-remapping functionality > of it is really a side-effect. It happens to change the user > because instead of altering the user to what it is in the backup, it > just accepts the default ownership based on the user it is connected as. And why do I need to care why things work like they totally make sense and I need them? :-) I just see that things work already besides one minor annoyance. So what is a side-effect in your opinion now could easily be communicated as feature as well. > You can accomplish the same thing by restoring as the superuser, > not having to alter the ownership of any internal type, and then > changing the ownership of the user-created objects in the new > database once it is restored.[...] But that is far more complicated, because one needs to know ALL objects in the restored schema to reown them manually. It takes more time because one needs to do it manually and for each object individually. It is error prone because one can easily miss things, especially if schemas are developed further over time etc. My current, already supported approach is far easier. I only need to take care about those CASTs manually now, nothing else yet. Mit freundlichen Grüßen, Thorsten Schöning -- Thorsten Schöning E-Mail: thorsten.schoen...@am-soft.de AM-SoFT IT-Systeme http://www.AM-SoFT.de/ Telefon...05151- 9468- 55 Fax...05151- 9468- 88 Mobil..0178-8 9468- 04 AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
Re: Multitenent architecture
Hi All, Our project uses each database for tenant, But how can we restrict tenant resources? Ex: Tenent1 has to use 20% resource and Tenent2 has to use 10% resource, how can we restrict users like this. Thanks and Regards, Vasu Madhineni On Mon, Jun 8, 2020 at 2:50 PM Laurenz Albe wrote: > On Sat, 2020-06-06 at 11:56 +0800, Vasu Madhineni wrote: > > > > On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe < > laurenz.a...@cybertec.at> wrote: > > > > > On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote: > > > > > > We are planning a POC on multitenant architecture in Postgres, > Could you please > > > > > > help us with steps for multitenant using schema for each > application model. > > > > > > > > > > For few tenants, you can keep identical tables in several schemas > and > > > > > set "search_path" to select a tenant. > > > > > > > > > > With many tenants, you are better off with one table that holds the > > > > > data for all clients. You can use Row Level Security to have each > > > > > tenant see only his or her data, and it might be a good idea to > > > > > use list partitioning on the tenant ID. > > > > Our environment is medical clinical data, so each clinic as a tenant. > > Approximately 500+ tenants with 6TB data. > > The important number to base this decision on would be the number of > tables you'd expect in the database. It shouldn't be too many. > > If the database grows large, you may be better off sharding the database > together with partitioning it across schemas. > Several smaller databases are easier to back up and make scaling easier. > > Of course that requires your application to be part of the solution: > it needs to know which database to use for which tenant. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Re: Logical replication from 11.x to 12.x and "unique key violations"
On 7/20/20 10:42 PM, Thomas Kellerer wrote: Adrian Klaver schrieb am 20.07.2020 um 16:45: On 7/20/20 7:22 AM, Thomas Kellerer wrote: I have a strange error when using logical replication between a 11.2 Where is "xxx_pkey" coming from, e.g. sequence? No, as mentioned, those are varchar(20) columns. The values are generated by the application (no default value defined for the column) Aah I see my mistake I was going off your follow up question not the original post. In that original post though you had the PK containing a varchar(100) column. Can we see the table schema and the PK definition for at least one of the tables that threw an error? -- Adrian Klaver adrian.kla...@aklaver.com
Switching Primary Keys to BigInt
Hi all,
We are running 9.6, and we are planning to move some primary keys from int
to bigint because we are approaching the type limit. We understand this
requires some downtime, but we want to know if there are things we can do
to limit it.
Here are our steps, with questions at the end.
ALTER TABLE some_table ADD COLUMN new_id bigint;
/* in batches, we update all the rows to new_id = id */
CREATE UNIQUE INDEX CONCURRENTLY some_table_pkey_new ON some_table(new_id);
/* take the apps down */
BEGIN;
LOCK TABLE some_table;
UPDATE some_table SET new_id = id WHERE new_id IS NULL;
ALTER SEQUENCE some_table_id_seq OWNED BY some_table.new_id;
ALTER TABLE some_table ALTER COLUMN new_id SET DEFAULT
nextval('some_table_id_seq'::regclass);
ALTER TABLE some_table DROP CONSTRAINT some_table_pkey;
ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY USING
INDEX some_table_pkey_new;
ALTER TABLE some_table DROP COLUMN id;
ALTER TABLE some_table RENAME COLUMN new_id to id;
COMMIT;
We are concerned with this step:
> ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY USING
INDEX some_table_pkey_new;
which requires a table scan. Is there a way to avoid that? Would a not null
constraint on new_id that is created as invalid first, then validated later
help us? I tried on a table with about 50 million records, and I see a drop
from 19 seconds spent on the alter to 8 seconds, which is inconclusive
(both after restarts for cold cache). Is there another way to tell? Or does
PG just have to do a sequential scan?
If the constraint idea works, we would probably need to add a trigger to
update new_id, but that's TBD.
Pgpool in docker container
Hi All, Planning to build standalone postgres and with pgpool as connection pooler in docker containers. Shall we try option like installing pgpool in one docker container and postgres in another docker container, is it possible? Thanks in advance. Regards, Vasu Madhineni
Re: Multitenent architecture
On Tue, Jul 21, 2020 at 7:47 AM Vasu Madhineni wrote: > Hi All, > > Our project uses each database for tenant, But how can we restrict > tenant resources? > Ex: Tenent1 has to use 20% resource and Tenent2 has to use 10% resource, > how can we restrict users like this. > See https://wiki.postgresql.org/wiki/Priorities -Michel
Re: Switching Primary Keys to BigInt
On 7/21/20 8:30 AM, Mohamed Wael Khobalatte wrote:
Hi all,
We are running 9.6, and we are planning to move some primary keys from
int to bigint because we are approaching the type limit. We understand
this requires some downtime, but we want to know if there are things we
can do to limit it.
Here are our steps, with questions at the end.
ALTER TABLE some_table ADD COLUMN new_id bigint;
/* in batches, we update all the rows to new_id = id */
CREATE UNIQUE INDEX CONCURRENTLY some_table_pkey_new ON some_table(new_id);
/* take the apps down */
BEGIN;
LOCK TABLE some_table;
UPDATE some_table SET new_id = id WHERE new_id IS NULL;
ALTER SEQUENCE some_table_id_seq OWNED BY some_table.new_id;
ALTER TABLE some_table ALTER COLUMN new_id SET DEFAULT
nextval('some_table_id_seq'::regclass);
ALTER TABLE some_table DROP CONSTRAINT some_table_pkey;
ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY USING
INDEX some_table_pkey_new;
ALTER TABLE some_table DROP COLUMN id;
ALTER TABLE some_table RENAME COLUMN new_id to id;
COMMIT;
Could you not simplify to something like this:
test_(aklaver)5432> create table change_seq(id serial PRIMARY KEY);
CREATE TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable |Default
+-+---+--+
id | integer | | not null |
nextval('change_seq_id_seq'::regclass)
Indexes:
"change_seq_pkey" PRIMARY KEY, btree (id)
test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
bigint;
ALTER TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable |Default
++---+--+
id | bigint | | not null |
nextval('change_seq_id_seq'::regclass)
Indexes:
"change_seq_pkey" PRIMARY KEY, btree (id)
test_(aklaver)5432> alter sequence change_seq_id_seq as bigint;
ALTER SEQUENCE
We are concerned with this step:
> ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY
USING INDEX some_table_pkey_new;
which requires a table scan. Is there a way to avoid that? Would a not
null constraint on new_id that is created as invalid first, then
validated later help us? I tried on a table with about 50 million
records, and I see a drop from 19 seconds spent on the alter to 8
seconds, which is inconclusive (both after restarts for cold cache). Is
there another way to tell? Or does PG just have to do a sequential scan?
If the constraint idea works, we would probably need to add a trigger to
update new_id, but that's TBD.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Switching Primary Keys to BigInt
On 7/21/20 11:17 AM, Adrian Klaver wrote:
On 7/21/20 8:30 AM, Mohamed Wael Khobalatte wrote:
Hi all,
> test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
bigint;
ALTER TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable | Default
++---+--+
id | bigint | | not null |
nextval('change_seq_id_seq'::regclass)
Indexes:
"change_seq_pkey" PRIMARY KEY, btree (id)
Forgot sequences are bigint by default. It would not hurt to check
pg_sequence just to make sure they are that. In that case the below is
not needed.
test_(aklaver)5432> alter sequence change_seq_id_seq as bigint;
ALTER SEQUENCE
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Switching Primary Keys to BigInt
Curious- what requires that the unique index be declared a primary key? What advantage does that give you? Just ensuring it isn't null? Side note- EOL for 9.6 is coming next year so just a plug for upgrading when possible, perhaps utilizing pglogical to get to v11 or 12. >
Re: Switching Primary Keys to BigInt
> > test_(aklaver)5432> alter table change_seq alter COLUMN id set data
type
> bigint;
> ALTER TABLE
> test_(aklaver)5432> \d change_seq
> Table "public.change_seq"
> Column | Type | Collation | Nullable |Default
> ++---+--+---
-
>
> id | bigint | | not null |
> nextval('change_seq_id_seq'::regclass)
> Indexes:
> "change_seq_pkey" PRIMARY KEY, btree (id)
This is significant downtime, since it locks exclusively, no? We want to
avoid that.
> Side note- EOL for 9.6 is coming next year so just a plug for upgrading
when possible, perhaps utilizing pglogical to get to v11 or 12.
Yep, we are painfully aware. The id growth will beat us to it, so we need
to deal with that first.
Re: Switching Primary Keys to BigInt
On 7/21/20 2:18 PM, Mohamed Wael Khobalatte wrote:
> test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
bigint;
ALTER TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable | Default
++---+--+
id | bigint | | not null |
nextval('change_seq_id_seq'::regclass)
Indexes:
"change_seq_pkey" PRIMARY KEY, btree (id)
This is significant downtime, since it locks exclusively, no? We want to
avoid that.
Yeah, I thought the int --> bigint would not do a table rewrite. Testing
showed otherwise. Forget that idea.
> Side note- EOL for 9.6 is coming next year so just a plug for
upgrading when possible, perhaps utilizing pglogical to get to v11 or 12.
Yep, we are painfully aware. The id growth will beat us to it, so we
need to deal with that first.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Switching Primary Keys to BigInt
> Yeah, I thought the int --> bigint would not do a table rewrite. Testing
> showed otherwise. Forget that idea.
Got it. Not sure what else we should consider. It seemed like the
constraint might be possible, but currently need a far bigger table to be
able to tell for sure, since we can't explain a DDL.
On Tue, Jul 21, 2020 at 7:32 PM Adrian Klaver
wrote:
> On 7/21/20 2:18 PM, Mohamed Wael Khobalatte wrote:
> >> > test_(aklaver)5432> alter table change_seq alter COLUMN id set data
> type
> >> bigint;
> >> ALTER TABLE
> >> test_(aklaver)5432> \d change_seq
> >> Table "public.change_seq"
> >> Column | Type | Collation | Nullable |Default
> >>
> ++---+--+
> >>
> >> id | bigint | | not null |
> >> nextval('change_seq_id_seq'::regclass)
> >> Indexes:
> >> "change_seq_pkey" PRIMARY KEY, btree (id)
> >
> > This is significant downtime, since it locks exclusively, no? We want to
> > avoid that.
>
> Yeah, I thought the int --> bigint would not do a table rewrite. Testing
> showed otherwise. Forget that idea.
>
> >
> > > Side note- EOL for 9.6 is coming next year so just a plug for
> > upgrading when possible, perhaps utilizing pglogical to get to v11 or 12.
> >
> > Yep, we are painfully aware. The id growth will beat us to it, so we
> > need to deal with that first.
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: Pgpool in docker container
On Tue, Jul 21, 2020 at 10:46 PM Vasu Madhineni wrote: > Hi All, > > Planning to build standalone postgres and with pgpool as connection pooler > in docker containers. > Shall we try option like installing pgpool in one docker container and > postgres in another docker container, is it possible? > As I know, the only way of communication between containers is through socket. But we are using pgpool to reduce the overhead in tcp/ip communication. Regards, Seenu. >
Re: Logical replication from 11.x to 12.x and "unique key violations"
Adrian Klaver schrieb am 21.07.2020 um 17:07:
>> No, as mentioned, those are varchar(20) columns.
>> The values are generated by the application (no default value defined for
>> the column)
>
> Aah I see my mistake I was going off your follow up question not the
> original post. In that original post though you had the PK containing
> a varchar(100) column. Can we see the table schema and the PK
> definition for at least one of the tables that threw an error?
>
Sorry about the confusion, some PKs are indeed defined as varchar(100) some as
varchar(20) and some as varchar(15)
And I was also wrong about the generation, there is indeed a default value
defined using a self-written ID generation function.
But during replication, that function isn't called, so it shouldn't matter, I
guess.
Here are two examples of failing tables:
CREATE TABLE IF NOT EXISTS emp_status
(
emp_status_id varchar(15) DEFAULT generate_id('EA') NOT NULL PRIMARY
KEY,
status_name varchar(20) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS company
(
comp_id varchar(15) DEFAULT generate_id('CO') NOT NULL PRIMARY KEY,
namevarchar(50) NOT NULL UNIQUE,
country varchar(50) NOT NULL,
codevarchar(20) NOT NULL
);
Both tables only contain only a few rows (less than 10) and e.g. for the status
lookup, the log entry was:
LOG: logical replication table synchronization worker for subscription "foo",
table "emp_status" has started
ERROR: duplicate key value violates unique constraint "emp_status_pkey"
DETAIL: Key (employee_available_status_id)=(BUJ4XFZ7ATY27EA) already exists.
CONTEXT: COPY employee_available_status, line 1
Thomas
