0

This is a web application which uses Postgres to store data pushed from various modules of the web application.

From Postgres side, how can I know what data entered/modified into Postgres? I mean if there is any Postgres data logging?

Postgres has 4-5 schemas & each schema has 2-3 tables. Each table has 10-20 records.

EDIT (8 Sept 2021):

  1. log_destination=csvlog - I tried different destinations to see if I'm getting the required logs. But all log destination are capturing the same logs.

2: reload - Did restarted server every time I made changes to postgresql.conf.
3: The logs are being generated at /var/lib/postgresql/data/pg_log directory with format postgresql-%Y-%m-%d_%H%M%S.log. Where latest logs are generated, but as mentioned in comment, I'm not getting all the query logs that are being executed on Postgres by the web application

4: I installed Postgres 9.6 as a Docker container.

5: I'm making changes in /var/lib/postgresql/data/postgresql.conf which is reflected in database query SELECT name, setting FROM pg_settings WHERE name LIKE '%log%'; after restart.

EDIT (7 Sept 2021):

name                       |setting                       |
---------------------------+------------------------------+
log_autovacuum_min_duration|-1                            |
log_checkpoints            |off                           |
log_connections            |off                           |
log_destination            |csvlog                        |
log_directory              |pg_log                        |
log_disconnections         |off                           |
log_duration               |off                           |
log_error_verbosity        |default                       |
log_executor_stats         |off                           |
log_file_mode              |0600                          |
log_filename               |postgresql-%Y-%m-%d_%H%M%S.log|
log_hostname               |off                           |
log_line_prefix            |                              |
log_lock_waits             |off                           |
log_min_duration_statement |-1                            |
log_min_error_statement    |error                         |
log_min_messages           |warning                       |
log_parser_stats           |off                           |
log_planner_stats          |off                           |
log_replication_commands   |off                           |
log_rotation_age           |1440                          |
log_rotation_size          |10240                         |
log_statement              |all                           |
log_statement_stats        |off                           |
log_temp_files             |-1                            |
log_timezone               |Etc/UTC                       |
log_truncate_on_rotation   |off                           |
logging_collector          |on                            |
syslog_facility            |local0                        |
syslog_ident               |postgres                      |
syslog_sequence_numbers    |on                            |
syslog_split_messages      |on                            |
wal_log_hints              |off                           |

EDIT

Improve this question
4
  • why don't you keep created_at, modified_at columns and when that modules saving the data in postgres will update the modified_at column if modified and created_at column will be set if data is created(this is for one time for a row). Commented Sep 1, 2021 at 15:55
  • This is an existing application and we're trying to fix a bug. If I want to add created_at, and modified_at, then I've to do several changes at application level, which might not be easy. Commented Sep 1, 2021 at 16:01
  • My Postgres version is PostgreSQL 9.6.22 on x86_64-pc-linux-gnu (Debian 9.6.22-1.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit| Commented Sep 7, 2021 at 7:13
  • @a_horse_with_no_name Thank you for highlighting. I've updated the tag in the OP. Commented Sep 7, 2021 at 8:13

1 Answer 1

Reset to default
1

Check a the logging configuration in postgresql.conf. You want to set up this What to log in particular log_statement. For your use the mod setting would be appropriate. Then you can look at the Postgres log to see what was changed and when. You might also want to set log_connections and log_disconnections to see what user the connection is running as.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I've tried doing these settings in postgresql.conf but I'm not getting log of all the queries that are being executed on the Postgres. There are many tables which gets updated (mainly INSERT) from web application inside Postgres, but those are not reflected into the log file. I've added my log settings in original post with EDIT as the character length of comments is not enough.
I've tried with log_statement="all" as well.
You have log_destination csvlog set which will log to a CSV file, is that what you want? Did you reload the server after making the changes to make then take effect? If you did reload and are not seeing queries then you are looking in the wrong Postgres log. How did you install Postgres? Where are you making the changes in postgresql.conf? Add the answers to as update to your question.
Updated answers to your question in OP under EDIT (8 Sept 2021):
I would turn on log_connections/log_disconnections. If you are not seeing any connections or queries from the Web app then you are looking at the wrong log file. It is not clear to me whether the log file you are looking at is in the host or the Docker container?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.