Re: query multiple schemas
On Sun, Apr 21, 2024 at 11:12 PM Tom Lane wrote: > Steve Baldwin writes: > > If the number of tenant schemas is reasonably static, you could write a > > plpgsql function to create a set of UNION ALL views > > Another idea is to build a partitioned table > Hi Norbert. I asked a [similar question][1] a while back, and unfortunately didn't get any actionable input, perhaps because I already mentioned in my message the options proposed here so far. Seems like people like us, using a dynamic number of schemas, are outliers in database-land. In my case, the revision/version of the schema could be different as well, which would complicate the partitioning idea. In any case, I'm interested in what works well for you. And if/when I get back to this issue myself, I'll do the same. Thanks, --DD [1]: https://postgrespro.com/list/thread-id/2673517
Re: query multiple schemas
> using a dynamic number of schemas
Although there are historical reasons behind our "schema per tenant"
architecture, it provides very good logical separation of data, and
is very convenient that we don't need to include the tenant ID in
each query (I'm sure that it would cause lots of bugs and trouble).
Besides, we use Hibernate and it
has great support for this architecture.
> In any case, I'm interested in what works well for you.
I went with the dynamic solution I proposed in my original email
(which performs a UNION of the queried table from all tenants).
Performance is currently not a priority in our use-cases, otherwise
I would have probably chosen a more static solution (which wouldn't
be easy to maintain continuously, I'm sure of).
I further simplified its usage, so finally it is fairly comfortable
to use either directly:
select tenantId, (record).*
from tenant_query(null::mytable)
where (record).type=2
order by tenantId, (record).name;
or by using a temporary view:
create temp view
all_tenant_mytable as
select tenantId, (record).* from tenant_query(null::mytable);
select *
from all_tenant_mytable
where type=2
order by tenantId, name;
> In my case, the revision/version of the schema could be
different as well
This complicates things very much, it is probably not possible to
implement a solution as comfortable as in my case.
Although I think the JSON-based solutions proposed in this thread
would work in your case as well.
In my current solution I also use JSON as intermediate
representation - although I feel it is because of my lack
of deeper knowledge of Postgresql's type system.
The difference is that you would need to use JSON as the final
representation, and reference the JSON fields using Postgres's
JSON operators.
> And if/when I get back to this issue myself, I'll do the
same.
My current solution is not much different than the one I posted in
my original question.
My main difficulty was the relatively static nature of Postgresql's
type system, so this solution is a result of lots of trial-and-error
rounds :)
Take a look at it, and you (and maybe others) may have
recommendations, e.g. how I could get rid of the usage of the
intermediate JSON "layer".
(Although I have to admit: it is amazing that it is possible to
implement this at all ;) )
CREATE OR REPLACE FUNCTION tenant_query_json(tbl anyelement)RETURNS setof json AS $func$declare _select text;begin _select := (select string_agg( format('select t.*, %L tenantId from %I.%I t', schema_name, schema_name, pg_typeof(tbl)), E'\n' || ' union all ' || E'\n') from ( SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema') and schema_name not like 'pg_%' ) tenants );
return query execute 'select row_to_json(r) from (' || _select || ') as r';END;$func$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION tenant_query(tbl anyelement)RETURNS table(tenantId text, record anyelement) AS $func$begin return query select t.tenantId, t.rec from ( selectjr->>'tenantid' tenantId,json_populate_record(tbl, jr) rec from tenant_query_json(tbl) jr ) t;END;$func$ LANGUAGE plpgsql;
As you can see in my examples above, I use the tenant_query() function but in your case
(if your schemas are different) something similar to tenant_query_json() may work better.
--
Norbi
On 2024. 04. 23. 9:33, Dominique
Devienne wrote:
On Sun, Apr 21, 2024 at 11:12 PM Tom Lane
wrote:
Steve
Baldwin
writes:
> If the number of tenant schemas is reasonably static,
you could write a
> plpgsql function to create a set of UNION ALL views
Another idea is to build a partitioned table
Hi Norbert. I asked a [similar question][1] a while back,
and unfortunately didn't get any actionable input,
perhaps
because I already mentioned in my message the options
proposed here so far. Seems like people like us, using a
dynamic number of schemas, are outliers in database-land.
In my case, the revision/version of the schema could be
different as well, which would complicate the
partitioning idea.
In any case, I'm interested in what works well for you.
And if/when I get back to this issue myself, I'll do the
same.
Thanks, --DD
Re: query multiple schemas
On Tue, Apr 23, 2024 at 11:08 AM Norbert Sándor wrote: > *> And if/when I get back to this issue myself, I'll do the same.* > My current solution is not much different than the one I posted in my > original question. > > CREATE OR REPLACE FUNCTION tenant_query_json(tbl anyelement) > > RETURNS setof json AS $func$ [...] > > > CREATE OR REPLACE FUNCTION tenant_query(tbl anyelement) > > RETURNS table(tenantId text, record anyelement) AS $func$ [...] > > Thanks for sharing Norbi. I'm not well versed in PG/PLsql, so using `anyelement` and `returns setof / table` is interesting to see. Regarding the type system, I don't know if PostgreSQL supports "structural" typing, i.e. types from different schemas, despite having the same "shape", are not interoperable. Thus your need to go through JSON to "anonymize" the types in your inner function, then de-anonymize them (to a schema's specific type) as a record, in your outer function. One solution in your case of homogenous tables types, is to centralize your types in a single data-less schema, and then create each tenants tables based on those types, i.e. all tables (across tenant schemas) share the same type. But that's speculation mostly. --DD I.e. 2nd form of CREATE TABLE, from https://www.postgresql.org/docs/current/sql-createtable.html: CREATE [...] TABLE [...] *table_name* OF *type_name*
Password forgotten
Hello. In my Debian box, after entering this command to psql-16, *psql -h localhost -U postgres* psql-16 asks for the password, which I have forgotten. So I get this message: *psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres" connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres" * Is there a way I can retrieve this master password? Thanks in advance. -- */ArbolOne.ca/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne on Java Development is in progress [ í ]
Re: Password forgotten
Have you tried sudo -u postgres psql On Tue, Apr 23, 2024 at 2:14 PM Arbol One wrote: > Hello. > In my Debian box, after entering this command to psql-16, *psql -h > localhost -U postgres* > > psql-16 asks for the password, which I have forgotten. > So I get this message: > > > > *psql: error: connection to server at "localhost" (::1), port 5432 failed: > FATAL: password authentication failed for user "postgres" connection to > server at "localhost" (::1), port 5432 failed: FATAL: password > authentication failed for user "postgres" * > > Is there a way I can retrieve this master password? > > Thanks in advance. > > > -- > *ArbolOne.ca* Using Fire Fox and Thunderbird. ArbolOne is composed of > students and volunteers dedicated to providing free services to charitable > organizations. ArbolOne on Java Development is in progress [ í ] > -- 𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚 ☏ ➁➁ ➅➃ ➇⓪ ➁⓪
Re: query multiple schemas
Em dom., 21 de abr. de 2024 às 17:12, Norbert Sándor <
sandor.norb...@erinors.com> escreveu:
> Hello,
>
> I have a database with multiple tenants with a separate schema for each
> tenant.
> The structure of each schema is identical, the tenant ID is the name of
> the schema.
>
> What I would like to achieve is to able to query tables in all schemas at
> once with the tenant IDs added to the result set.
>
I have a similar structure and do my multi tenant queries this way.
The only problem I see is that we have to define every result type, because
I return a record, but it runs fine.
create function sql_per_tenant(sql text, tenants text[]) returns setof
record language plpgsql AS $function$
declare
Result record;
schemas text;
begin
for schemas in select unnest(tenants) loop
execute Format('set local search_path to %s, public;', schemas);
for Result in execute Format('select $$%1$s$$ tenant, * from (%2$s) x',
schemas, sql) loop
return next Result;
end loop;
end loop;
end;$function$;
select * from sql_per_tenant('select Order_ID, sum(Value) from Orders inner
join Items using(Order_ID)
where Due_Date = Current_Date','{cus_001,cus_035,cus_175}')
as (SchemaName text, Order_ID integer, sum_of_items Numeric)
regards
Marcos
Re: Password forgotten
On Tuesday, April 23, 2024, Arbol One wrote: > Hello. > In my Debian box, after entering this command to psql-16, *psql -h > localhost -U postgres* > > psql-16 asks for the password, which I have forgotten. > So I get this message: > > > > *psql: error: connection to server at "localhost" (::1), port 5432 failed: > FATAL: password authentication failed for user "postgres" connection to > server at "localhost" (::1), port 5432 failed: FATAL: password > authentication failed for user "postgres" * > > Is there a way I can retrieve this master password? > Nope. You need to login using a method that doesn’t require a password then change the password. See pg_hba.conf for authentication options. Usually connecting via local and peer authentication is the default option. David J.
Re: Password forgotten
"David G. Johnston" writes: > On Tuesday, April 23, 2024, Arbol One wrote: >> psql-16 asks for the password, which I have forgotten. >> Is there a way I can retrieve this master password? > Nope. You need to login using a method that doesn’t require a password > then change the password. See pg_hba.conf for authentication options. > Usually connecting via local and peer authentication is the default option. If all else fails, shut the server down and start it in single-user mode. That will let you in as a superuser and you can use ALTER USER to reset the role's password. regards, tom lane
Re: Password forgotten
On Tue, Apr 23, 2024 at 08:14:15AM -0400, Arbol One wrote: > Hello. > In my Debian box, after entering this command to psql-16, *psql -h localhost > -U postgres* You might want to read https://www.depesz.com/2008/11/28/recovering-lost-postgresql-password/ Best regards, depesz
Logical Replication Delay on Remote Server
Hi Team, I am facing replication lag in postgres16 at times,not able to find the reason. Please find the configuration: 1) Two replication slots for two servers (1 is the same data center, another remote) 2) weekly once/twice facing lag(around 2GB for 1k TPS environment) in the remote replication slot and another same data center server slot with 0 lag. My observation: 1) Noticed pg_stat_replication_slot total_txn reduced from 1k to 5 or 6 but other slot is same with 1k TPS 2)And noticed lag for remote slot but fine with same data center 3) Most importantly I have plenty of Bandwidth available in n/w ,2GB is still freely available out of 4GB network. 4)No IO issues on servers I am not able to prove if this is due to Network .Can you help me how to proceed on this? At Least how the logical decoding total_txn is counting the view pg_stat_replication_slot? -- Thanks & Regards, Ram.
Re: Logical Replication Delay on Remote Server
Hi Team, adding one more point here, 5. Pg_stat_replication_slot total_txn count reduces when lag occurs .Does decoding stop in case of logical replication delay? On Tue, 23 Apr 2024 at 22:55, Ramakrishna m wrote: > Hi Team, > > I am facing replication lag in postgres16 at times,not able to find the > reason. > > Please find the configuration: > 1) Two replication slots for two servers (1 is the same data center, > another remote) > 2) weekly once/twice facing lag(around 2GB for 1k TPS environment) in the > remote replication slot and another same data center server slot with 0 > lag. > > My observation: > > 1) Noticed pg_stat_replication_slot total_txn reduced from 1k to 5 or 6 > but other slot is same with 1k TPS > 2)And noticed lag for remote slot but fine with same data center > 3) Most importantly I have plenty of Bandwidth available in n/w ,2GB is > still freely available out of 4GB network. > 4)No IO issues on servers > I am not able to prove if this is due to Network .Can you help me how to > proceed on this? > At Least how the logical decoding total_txn is counting the view > pg_stat_replication_slot? > > -- > Thanks & Regards, > Ram. > -- Thanks & Regards, Ram.
