Lookup Primary Key of Foreign Server's Table
I'm looking for a system query that will lookup the primary key column on a
fdw table. It's possible we need to declare that part of the foreign
table's schema in the local (is that the right term?) database?
Here's the foreign table - I don't see anything showing a primary key, so
my hunch is we need to declare it in the local schema?
*=> \d sidecar_link.actions
Foreign table "sidecar_link.actions" Column |Type
| Collation | Nullable | Default
| FDW options
-+-+---+--+--+-
id
| bigint | | not null |
nextval('sidecar_link.actions_id_seq'::regclass) | user_session_id |
bigint | | not null |
| user_id | bigint
| | not null |
| created_at | timestamp without time zone | | not null |
now()| occurred_at |
timestamp without time zone | | not null | now()
| thing_id| integer
| | | |
parent_thing_id | integer | | |
| viewing_id | integer
| | |
| origin | origin |
| | 'mysteryscience'::origin | scope
| text| | not null |
| name| text
| | not null |
| details | text| |
| | request_path
| text| | |
| Server: pg_mysterysci_sidecarFDW options:
(schema_name 'public', table_name 'actions')*
Not really related question, but a curiosity: why does this table not show
in the list of foreign tables?
*=> \det List of foreign tables Schema | Table | Server
+---+(0 rows)*
Re: Lookup Primary Key of Foreign Server's Table
The foreign table has a primary key. Ruby on Rails uses a system query to lookup what the primary key on the table is, but it's querying the local database, and not finding anything. In a schema dump of the local database, I don't see a primary key defined, so I'm presuming I need to issue an ADD CONSTRAINT command against the foreign table to reflect what is actually true in the foreign table? Is that correct?
Re: Lookup Primary Key of Foreign Server's Table
Yeah. Part of my confusion is due to Heroku providing a Data Links service that handles a lot of the internal details around establishing a foreign server connection, and not knowing exactly what to expect. I experimented with IMPORT FOREIGN SCHEMA in a couple of test databases and noticed that there was no declaration of primary key brought over to the local database. So, this is something ActiveRecord simply won't be able to handle right now, and I have to take care of that myself, which is easy enough to do. Thx for the feedback. On Wed, Oct 23, 2019 at 11:12 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Oct 23, 2019 at 8:47 AM Chris Morris > wrote: > >> The foreign table has a primary key. Ruby on Rails uses a system query to >> lookup what the primary key on the table is, but it's querying the local >> database, and not finding anything. In a schema dump of the local database, >> I don't see a primary key defined, so I'm presuming I need to issue an ADD >> CONSTRAINT command against the foreign table to reflect what is actually >> true in the foreign table? Is that correct? >> > > The documentation says doing what you suggest won't work... > > David J. > >
PG 12: Partitioning across a FDW?
Is it even possible to use PG partitioning across a Foreign Server?
Re: PG 12: Partitioning across a FDW?
> > > Is it even possible to use PG partitioning across a Foreign Server? > I am not certain what you mean, but you can have foreign tables as > partitions > of a partitioned table. The partitions won't be processed in parallel > though. I have a large, growing table, that I'd like to start partitioning, but also would like "older" partitions to be stored in a separate database, connected via FDW. I haven't played around with partitioning at all yet, so clearly I'm not sure how to ask the question :) The hope is to still have one "seamless" table users can query, but to spread the storage across different databases. I realize that may be asking for too much.
Re: PG 12: Partitioning across a FDW?
> > Not yet.. There is ongoing work to make that happen though. Glad to hear it. :) Thx.
Re: PG 12: Partitioning across a FDW?
Right now my dbs are hosted by Heroku, so I doubt I have any control over the dbs at that level. Thanks for the idea though! :) On Wed, Mar 25, 2020 at 12:04 PM Michael Lewis wrote: > Chris, > Does it actually need to be a different server and database, or would it > be possible to have another storage device added to your existing database > and make use of tablespaces to accomplish pseudo-archive of older > partitions? Just a thought. > >>
Log Unique Queries without Params?
I have a local script I've written that will scan a log of PG queries to extract out unique queries without any specific parameter data. For example, if these 2 queries are actually run: SELECT * FROM foo where bar = 1; SELECT * FROM foo where bar = 2; It will capture only: SELECT * FROM foo whee bar = :id; Are there any existing tools that do this already for me? I'm considering setting up a server that can have logs forwarded to it and only logging unique queries like this, but I don't want to build anything that may already exist out there.
Re: Log Unique Queries without Params?
Thx! On Sat, Apr 11, 2020 at 11:55 PM Julien Rouhaud wrote: > On Sun, Apr 12, 2020 at 6:51 AM Chris Morris > wrote: > > > > I have a local script I've written that will scan a log of PG queries to > extract out unique queries without any specific parameter data. For > example, if these 2 queries are actually run: > > > > SELECT * FROM foo where bar = 1; > > SELECT * FROM foo where bar = 2; > > > > It will capture only: > > > > SELECT * FROM foo whee bar = :id; > > > > Are there any existing tools that do this already for me? I'm > considering setting up a server that can have logs forwarded to it and only > logging unique queries like this, but I don't want to build anything that > may already exist out there. > > pgbadger (http://pgbadger.darold.net/#about) will do that and much > more. Depending on what you want to achieve maybe pg_stat_statements > (https://www.postgresql.org/docs/current/pgstatstatements.html) is > also an alternative. >
Audit Role Connections
We're using Heroku's PG, and it comes with a default, almost super-user. I'm wanting to restrict that account for only our production app servers, and have anyone connecting for personal queries to go through an assigned role by employee (that's all setup and working). Other than polling pg_stat_activity (which isn't 100% accurate depending on timing), is there a good way to audit connections? To detect which roles are being used for connections?
Re: Audit Role Connections
Ah, I do appear to have that enabled (inside Heroku's config), but I can't find anything like that in the logs, so I've opened a ticket with them. Thx a lot! On Fri, May 29, 2020 at 2:25 PM Peter J. Holzer wrote: > On 2020-05-29 12:42:47 -0500, Chris Morris wrote: > > We're using Heroku's PG, > [...] > > Other than polling pg_stat_activity (which isn't 100% accurate depending > on > > timing), is there a good way to audit connections? To detect which roles > are > > being used for connections? > > Do you have access to the log files? > > If you log_connections is on, you get messages like these: > > > 2020-05-29 21:00:02 CEST [27995]: [2-1] user=w*,db=wds,pid=27995 LOG: > connection authorized: user=w* database=wds > 2020-05-29 21:00:18 CEST [27995]: [9-1] user=w*,db=wds,pid=27995 LOG: > disconnection: session time: 0:00:15.979 user=w* database=wds > host=[local] > 2020-05-29 21:07:14 CEST [7481]: [2-1] user=u*,db=wds,pid=7481 LOG: > connection authorized: user=u* database=wds > 2020-05-29 21:07:14 CEST [7481]: [7-1] user=u*,db=wds,pid=7481 LOG: > disconnection: session time: 0:00:00.016 user=u* database=wds > host=[local] > 2020-05-29 21:10:56 CEST [13918]: [2-1] user=m***,db=wds,pid=13918 > LOG: connection authorized: user=m*** database=wds SSL enabled > (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, > compression=off) > 2020-05-29 21:10:56 CEST [13918]: [11-1] user=m***,db=wds,pid=13918 > LOG: disconnection: session time: 0:00:00.117 user=m*** database=wds > host=143.130.**.** port=54037 > > (user names and IP addresses censored for privacy reasons) > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
