Postgresql – using COPY command and wal_buffers

bulkcopypostgresql

since COPY command runs inside a single transaction, if its WAL data doesn't fit into wal_buffers? for example if we are bulk importing from CSV file.

Best Answer

As the documentation states:

There are two commonly used internal WAL functions: LogInsert and LogFlush. LogInsert is used to place a new record into the WAL buffers in shared memory. If there is no space for the new record, LogInsert will have to write (move to kernel cache) a few filled WAL buffers. This is undesirable because LogInsert is used on every database low level modification (for example, row insertion) at a time when an exclusive lock is held on affected data pages, so the operation needs to be as fast as possible. What is worse, writing WAL buffers might also force the creation of a new log segment, which takes even more time. Normally, WAL buffers should be written and flushed by a LogFlush request, which is made, for the most part, at transaction commit time to ensure that transaction records are flushed to permanent storage. On systems with high log output, LogFlush requests might not occur often enough to prevent LogInsert from having to do writes. On such systems one should increase the number of WAL buffers by modifying the configuration parameter wal_buffers.

Which means for me that in such a situation the system will need more time to complete the COPY because of the writes.

Related Solutions

Postgresql – Import data extracted from website directly into PostgreSQL database without using any text or csv file

You can open a pipe and write your data to the pipe directly. Related answer with demo code:

Insert SQL statements via command line without reopening connection to remote database

Or you can use COPY with a program or standard input directly:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
...

When PROGRAM is specified, the server executes the given command and reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.

Postgresql – Why doesn’t postgresql COPY command work with files in mapped drives

COPY runs as the PostgreSQL server, which usually runs as NETWORKSERVICE on Windows. This is a local service account that does not have access to your user account's network login credentials for shared drives.

Use the psql command \copy, which reads the file from the client application, running under your user ID and with access to your shared drives, then streams it over the client/server network connection to the postgres server.

I think PgAdmin-III has a similar CSV import command that uses COPY ... FROM STDIN like psql's \copy does, too.

If you're on a Windows AD Domain and you really need PostgreSQL to have direct access to shares, you can run the PostgreSQL server under a domain account instead. But that's usually unnecessary.

Best Answer

Related Solutions

Postgresql – Import data extracted from website directly into PostgreSQL database without using any text or csv file

Postgresql – Why doesn’t postgresql COPY command work with files in mapped drives

Related Question