Running pg_dump while insert queries are still running?

Question

If I run pg_dump to dump a table into a SQL file, does it take a snapshot of the last row in the table, and dump all the rows up to this row?

Or does it keep dumping all the rows, even those that were inserted after pg_dump was ran?

A secondary question is: Is it a good idea to stop all insert queries before running pg_dump?

Impulse

Answer 1

It will obtain a shared lock on your tables when you run the pg_dump. Any transactions completed after you run the dump will not be included. So when the dump is finished, if there are current transactions in process that haven't been committed, they won't be included in the dump.

There is another pg_dump option with which it can be run:

--lock-wait-timeout=timeout

Do not wait forever to acquire shared table locks at the beginning of the dump. Instead fail if unable to lock a table within the specified timeout. The timeout may be specified in any of the formats accepted by SET statement_timeout. (Allowed formats vary depending on the server version you are dumping from, but an integer number of milliseconds is accepted by all versions.)

Seftton

What if a transaction started before pg_dump started and finished after pg_dump started but before it finished? – Katakana

If the transaction wasn't finished before your dump started then it won't be included – Seftton

Or for that matter, what if pg_dump starts, transaction starts, transaction finishes, pg_dump finishes? – Katakana

When you run pg_dump, you are dumping the databases in the state that it was in precisely when you run it - it leaves it in a consistent state. So any open transactions won't be included. And to clarify - any transactions started and completed after pg_dump is run won't be included either – Seftton

Ok, thanks - I'm just clarifying because the first paragraph in your answer is a bit ambiguous. You might want to mention that pg_dump operates on a snapshot of the database which includes all transactions committed before pg_dump started or something along those lines – Katakana