如何在psql中获取过去1小时记录？求助排查Internal 1报错

Hey there! Let's tackle your two questions one by one, with practical steps and explanations tailored to psql/PostgreSQL.

The exact query depends on the data type of your timestamp column (either timestamp or timestamptz). Here are the most common scenarios:

• If your column is timestamp (without timezone):
  Use NOW() to get the server's current time, then subtract a 1-hour interval. Just make sure your column's timestamps align with the server's system time.

  SELECT * FROM your_table 
  WHERE created_at >= NOW() - INTERVAL '1 hour';
  

• If your column is timestamptz (with timezone):
  This accounts for timezone differences. You can use NOW() (which returns a timestamptz value) or CURRENT_TIMESTAMP (they're equivalent here):

  SELECT * FROM your_table 
  WHERE created_at >= CURRENT_TIMESTAMP - INTERVAL '1 hour';
  

Don’t forget to replace your_table and created_at with your actual table name and timestamp column name!

An "Internal 1" error is PostgreSQL’s generic way of saying something went wrong internally—let’s break down how to diagnose and fix this:

• First, check the PostgreSQL logs
  This is the most critical step. The generic error message hides specific details (like stack traces or failure points) that logs will show. On most Linux systems, logs live at paths like /var/log/postgresql/postgresql-<version>-main.log. Use this command to view recent entries:

  tail -n 50 /var/log/postgresql/postgresql-14-main.log
  

  (Adjust the version number to match your PostgreSQL installation.)

• Verify system resource availability
  This error often pops up when the server runs out of CPU, memory, or disk space. Use these quick checks:

  • top: Monitor real-time CPU and memory usage
  • df -h: Check if any disk partition is full

• Double-check your query syntax
  A malformed query can trigger unexpected internal errors. Confirm:

  • Your timestamp column name is spelled correctly
  • The interval syntax is valid (it’s INTERVAL '1 hour', not '1 hours')
  • You’re not mixing incompatible data types (e.g., comparing a date column to a timestamp value)

• Check for database corruption
  If logs hint at data corruption, try these steps (always back up production data first):

  • Run VACUUM ANALYZE your_table; to clean up dead rows and update query statistics
  • Use REINDEX TABLE your_table; to rebuild indexes if they’re corrupted
  • Test table integrity by creating a dump: pg_dump -t your_table your_db > table_dump.sql—if the dump fails, corruption is likely.

• Rule out extension/plugin issues
  If you’re using third-party extensions (like PostGIS, pg_stat_statements), ensure they’re compatible with your PostgreSQL version. Try disabling the extension temporarily (if safe) to see if the error disappears.

• Restart PostgreSQL as a last resort
  Temporary glitches in the server process can cause this error. Restart the service with:

  sudo systemctl restart postgresql
  

内容的提问来源于stack exchange，提问作者Sach jot