Re: pgaudit log directory
On 18/11/19 9:56 μ.μ., Dave Hughes wrote: Hello, I'm using PostgreSQL 10.5 on Linux (RHEL). I'm new to administering PostgreSQL and recently installed pgaudit. I believe I have it installed correctly and wanted to start playing with it to see how exactly it works. So while walking through a tutorial I found online, I saw where I can enter a statement in PostgreSQL, such as: ALTER SYSTEM SET pgaudit.log TO 'read, write'; SELECT pg_reload_conf(); Then after reading or writing to a table, you can then check "pg_log" for the audit entries. But my issue is that I can't find the log file at all? In my main PostgreSQL directory (/work/PostgreSQL/10)I do have a file called "logfile", but there are no entries from today. When I go into the pgaudit sub-directory (/work/PostegreSQL/10/pgaudit) I don't see any log file in there either? pgaudit writes in the standard pgsql log. Can someone point me in the right direction? Thanks, Dave Hughes -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: REINDEX VERBOSE unknown option
On Mon, 18 Nov 2019 at 22:24, Peter J. Holzer wrote:
>
> On 2019-11-18 12:24:40 +, Geoff Winkless wrote:
> > On Mon, 18 Nov 2019 at 11:46, Michael Paquier wrote:
> > > On Mon, Nov 18, 2019 at 10:27:24AM +0100, Josef Šimánek wrote:
> > > > This is clear once you understand what does it mean. I was aware of
> > > > VERBOSE
> > > > option of EXPLAIN and tried to use it without needed parentheses (the
> > > > same
> > > > way EXPLAIN can understand it). In the parameter list of REINDEX, it is
> > > > still called VERBOSE (not "( VERBOSE )") and there's no info
> > > > that parentheses are needed.
> [...]
> > But if the parentheses are part of the parameter, I think putting the
> > parentheses in the parameter list might be a good compromise.
>
> The parentheses aren't part of the parameter. They are part of the
> syntax of the command.
Then at the very least draw attention to the fact that the parentheses
are required in the description of the parameter in the notes.
It's bad enough that you have the inconsistency that REINDEX VERBOSE
requires parentheses while the more recent REINDEX CONCURRENTLY does
not (presumably to match the syntax of CREATE INDEX CONCURRENTLY),
without insisting that the user parse the difference between { and (
in the manual (not the easiest difference to scan) before they can use
the command.
> How about this?
>
> * Terminals (stuff that has to be typed as shown) in bold.
>
> * Non-Terminals (stuff which has to be replaced) in italic.
>
> * Meta-characters ([, ], |, ...) in regular type.
Even if you do that you're still requiring the user to parse syntax
according to esoteric rules. I'm not sure that changing the rules
helps that much.
Geoff
Re: pgaudit log directory
Thanks for the response! I realized I didn't have the default logging turned on. I needed to edit the postgresql.conf file to enable log_destination = 'csvlog' and logging_collector = on. Once I did that I can now see the audit file. On Tue, Nov 19, 2019 at 4:31 AM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 18/11/19 9:56 μ.μ., Dave Hughes wrote: > > Hello, > > I'm using PostgreSQL 10.5 on Linux (RHEL). I'm new to administering > PostgreSQL and recently installed pgaudit. I believe I have it installed > correctly and wanted to start playing with it to see > > how exactly it works. > > > > So while walking through a tutorial I found online, I saw where I can > enter a statement in PostgreSQL, such as: > > ALTER SYSTEM SET pgaudit.log TO 'read, write'; > > SELECT pg_reload_conf(); > > Then after reading or writing to a table, you can then check "pg_log" > for the audit entries. But my issue is that I can't find the log file at > all? > > > > In my main PostgreSQL directory (/work/PostgreSQL/10)I do have a file > called "logfile", but there are no entries from today. When I go into the > pgaudit sub-directory (/work/PostegreSQL/10/pgaudit) > > I don't see any log file in there either? > > pgaudit writes in the standard pgsql log. > > > > > Can someone point me in the right direction? > > > > Thanks, > > Dave Hughes > > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt > > > >
Help with configuring pgAudit
Hello, I'm using PostgreSQL 10.5 on Linux (RHEL). I recently installed pgAudit and was trying to configure it to capture DLL statements. 1) The first thing I tried was to edit the postgresql.conf file directly. I didn't see any commented out default entries to edit, so near where I have the entries: log_destination = 'csvlog' log_collector = on I entered my own entry of: pgaudit.log = 'ddl' Then I restarted the database. After doing this I created and dropped a table, but no entry of that in the log file postgresql-2019-11-19-141901.csv. 2) So then tried to create the entry using the ALTER command: ALTER SYSTEM SET pgaudit.log TO 'ddl'; SELECT pg_reload_conf(); After doing this, I noticed a second file was created (postgresql.auto.conf). That file had the entry pgaudit.log = 'ddl'. From what I read, this file get read after the main postgresql.conf file. However after creating and dropping a table, still no entry in the log file. I did notice though when I run the command: show pgaudit.log; It came back with 'Role' (and not ddl). 3) So the final thing I tried was to enter the command: set pgaudit.log = 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'. This time I tried to drop a table again. After the table was dropped , my psql client displayed: NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLEblah,blah. This looks like a pgaudit entry but was on my screen and not in the log file. At this point I restarted the database again and now when I run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl). Can someone please help me see what it is i'm doing incorrectly? Thanks in advance, Dave Hughes
Re: Help with configuring pgAudit
Hello Dave, What I can see is you missed to include pgAudit extension in shared_preload_libraries parameter (*shared_preload_libraries='pgaudit'*). Thanks Rajni On Wed, Nov 20, 2019 at 7:39 AM Dave Hughes wrote: > Hello, > I'm using PostgreSQL 10.5 on Linux (RHEL). I recently installed pgAudit > and was trying to configure it to capture DLL statements. > > 1) The first thing I tried was to edit the postgresql.conf file directly. > I didn't see any commented out default entries to edit, so near where I > have the entries: > log_destination = 'csvlog' > log_collector = on > I entered my own entry of: > pgaudit.log = 'ddl' > Then I restarted the database. After doing this I created and dropped a > table, but no entry of that in the log file > postgresql-2019-11-19-141901.csv. > > 2) So then tried to create the entry using the ALTER command: > ALTER SYSTEM SET pgaudit.log TO 'ddl'; > SELECT pg_reload_conf(); > After doing this, I noticed a second file was created > (postgresql.auto.conf). That file had the entry pgaudit.log = 'ddl'. From > what I read, this file get read after the main postgresql.conf file. > However after creating and dropping a table, still no entry in the log > file. I did notice though when I run the command: show pgaudit.log; It > came back with 'Role' (and not ddl). > > 3) So the final thing I tried was to enter the command: set pgaudit.log = > 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'. > This time I tried to drop a table again. After the table was dropped , my > psql client displayed: > NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLEblah,blah logged>. This looks like a pgaudit entry but was on my screen and not in > the log file. At this point I restarted the database again and now when I > run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl). > > Can someone please help me see what it is i'm doing incorrectly? > > Thanks in advance, > Dave Hughes > >
postgres backup question
Hello, What is the best way to dump/restore the entire database except couple of largest tables, what is the best way to do it on PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu? Thank you
Re: postgres backup question
On 11/19/19 4:06 PM, Julie Nishimura wrote: Hello, What is the best way to dump/restore the entire database except couple of largest tables, what is the best way to do it on PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu? https://www.postgresql.org/docs/11/app-pgdump.html -T table --exclude-table=table Do not dump any tables matching the table pattern. The pattern is interpreted according to the same rules as for -t. -T can be given more than once to exclude tables matching any of several patterns. When both -t and -T are given, the behavior is to dump just the tables that match at least one -t switch but no -T switches. If -T appears without -t, then tables matching -T are excluded from what is otherwise a normal dump. Thank you -- Adrian Klaver adrian.kla...@aklaver.com
mysysconf ?
I am presently running on a Ubuntu 18.04 instance, and as you know Debian/Ubuntu have upgraded to version 12. i have not completed the upgrade yet, so I am in the situation of still having a version 11 server, attaching from vversion 12 psql. I was troubleshooting something a few minutes ago, and wound up looking t the logfiles. I discovered that every time I connect like this I get an error about the msysconf relation not existing. This appears to be harmless, but I was wondering what the story on this is? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: mysysconf ?
On 11/19/19 4:32 PM, stan wrote: I am presently running on a Ubuntu 18.04 instance, and as you know Debian/Ubuntu have upgraded to version 12. i have not completed the upgrade yet, so I am in the situation of still having a version 11 server, attaching from vversion 12 psql. I was troubleshooting something a few minutes ago, and wound up looking t the logfiles. I discovered that every time I connect like this I get an error about the msysconf relation not existing. This appears to be harmless, but I was wondering what the story on this is? Access trying to connect via ODBC: https://www.postgresql.org/message-id/ofcecdfa08.38a13a4f-onc1256c5d.00544...@axxessit.no -- Adrian Klaver adrian.kla...@aklaver.com
Re: REINDEX VERBOSE unknown option
On Tue, Nov 19, 2019 at 11:37:04AM +, Geoff Winkless wrote:
> It's bad enough that you have the inconsistency that REINDEX VERBOSE
> requires parentheses while the more recent REINDEX CONCURRENTLY does
> not (presumably to match the syntax of CREATE INDEX CONCURRENTLY),
> without insisting that the user parse the difference between { and (
> in the manual (not the easiest difference to scan) before they can use
> the command.
The first implementations of REINDEX CONCURRENTLY used the existing
grammar. There was also a discussion when the feature was close to
commit about exactly that and I preferred the parenthesis option.
Who won t the end? Consistency with the existing grammar for
CREATE/DROP INDEX here.
>> How about this?
>>
>> * Terminals (stuff that has to be typed as shown) in bold.
>>
>> * Non-Terminals (stuff which has to be replaced) in italic.
>>
>> * Meta-characters ([, ], |, ...) in regular type.
>
> Even if you do that you're still requiring the user to parse syntax
> according to esoteric rules. I'm not sure that changing the rules
> helps that much.
This does not concern only the page for REINDEX. Perhaps this could
be improved, but I am not sure how and particularly if changing it is
worth it as many people are used to the existing way of presenting the
commands synopsis as well.
--
Michael
signature.asc
Description: PGP signature
Re: PostgreSQL - unrecognized win32 error code: 38
On Wed, Oct 30, 2019 at 12:13 AM Thomas Munro wrote:
> On Tue, Oct 29, 2019 at 9:23 PM ZhenHua Cai wrote:
> > No, it doesn't call any in-core code.
>
> I wondered if this could be coming from the new code in
> src/port/pg_p{read,write}.c. ERROR_HANDLE_EOF is a documented
> GetLastError() return value after ReadFile() fails[1], but only for
> asynchronous files. We are using that interface ("overlapped" IO,
> their name for asynchronous IO, but the same interface can also do
> synchronous IO and happens to support an offset like Unix's pread()),
> but we're not opening file handles with FILE_FLAG_OVERLAPPED so we
> have a plain old synchronous handle here.
> [1]
> https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-readfile
Hi,
With a little help we got to the bottom of this:
https://www.postgresql.org/message-id/flat/CAC%2BAXB0_zNFUH1BrRGKUkFxvq3SxsojroJN70iTUA1gUNn_gag%40mail.gmail.com#cb324760c6a142ec4ed6abefa8e68f18
It should be fixed in the next release. Thanks for the report!
How should I specify work_mem/max_worker_processes if I want to do big queries now and then?
Hello, I am doing a query to fetch about 1000 records in one time. But the query seems very slow, like "mission impossible". I am very confident that these records should be fit into my shared_buffers settings(20G), and my query is totally on my index, which is this big:(19M x 100 partitions), this index size can also be put into shared_buffers easily.(actually I even made a new partial index which is smaller and delete the bigger old index) This kind of situation makes me very disappointed.How can I make my queries much faster if my data grows more than 1000 in one partition? I am using pg11.6. Many thanks, James
