# Using the log\$1fdw extension to access the DB log using SQL Aurora PostgreSQL DB cluster supports the `log_fdw` extension, which you can use to access your database engine log using a SQL interface. The `log_fdw` extension provides two functions that make it easy to create foreign tables for database logs: + `list_postgres_log_files` – Lists the files in the database log directory and the file size in bytes. + `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – Builds a foreign table for the specified file in the current database. All functions created by `log_fdw` are owned by `rds_superuser`. Members of the `rds_superuser` role can grant access to these functions to other database users. By default, the log files are generated by Amazon Aurora in `stderr` (standard error) format, as specified in `log_destination` parameter. There are only two options for this parameter, `stderr` and `csvlog` (comma-separated values, CSV). If you add the `csvlog` option to the parameter, Amazon Aurora generates both `stderr` and `csvlog` logs. This can affect the storage capacity on your DB cluster, so you need to be aware of the other parameters that affect log handling. For more information, see [Setting the log destination (`stderr`, `csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format). One benefit of generating `csvlog` logs is that the `log_fdw` extension lets you build foreign tables with the data neatly split into several columns. To do this, your instance needs to be associated with a custom DB parameter group so that you can change the setting for `log_destination`. For more information about how to do so, see [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md). The following example assumes that the `log_destination` parameter includes `cvslog`. **To use the log\$1fdw extension** 1. Install the `log_fdw` extension. ``` postgres=> CREATE EXTENSION log_fdw; CREATE EXTENSION ``` 1. Create the log server as a foreign data wrapper. ``` postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw; CREATE SERVER ``` 1. Select all from a list of log files. ``` postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1; ``` A sample response is as follows. ``` file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2023-08-09-22.csv | 1111 postgresql.log.2023-08-09-23.csv | 1172 postgresql.log.2023-08-10-00.csv | 1744 postgresql.log.2023-08-10-01.csv | 1102 (4 rows) ``` 1. Create a table with a single 'log\$1entry' column for the selected file. ``` postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2023-08-09-22.csv'); ``` The response provides no detail other than that the table now exists. ``` ----------------------------------- (1 row) ``` 1. Select a sample of the log file. The following code retrieves the log time and error message description. ``` postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1; ``` A sample response is as follows. ``` log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows) ```