Running EDB*Loader v17

Use the following command to invoke EDB*Loader from the command line:

edbldr [ -d <dbname> ] [ -p <port> ] [ -h <host> ]
[ USERID={ <username/password> | <username>/ | <username> | / } ]
[ { -c | connstr= } <CONNECTION_STRING> ]
  CONTROL=<control_file>
[ DATA=<data_file> ]
[ BAD=<bad_file>]
[ DISCARD=<discard_file> ]
[ DISCARDMAX=<max_discard_recs> ]
[ HANDLE_CONFLICTS={ FALSE | TRUE } ]
[ LOG=<log_file> ]
[ PARFILE=<param_file> ]
[ DIRECT={ FALSE | TRUE } ]
[ FREEZE={ FALSE | TRUE } ]
[ ERRORS=<error_count> ]
[ PARALLEL={ FALSE | TRUE } ]
[ ROWS=<n> ]
[ SKIP=<skip_count> ]
[ SKIP_INDEX_MAINTENANCE={ FALSE | TRUE } ]
[ edb_resource_group=<group_name> ]

Description

You can specify parameters listed in the syntax diagram in a parameter file. Exeptions include the -d option, -p option, -h option, and the PARFILE parameter. Specify the parameter file on the command line when you invoke edbldr using PARFILE=param_file. You can specify some parameters in the OPTIONS clause in the control file. For more information on the control file, see Building the EDB*Loader control file.

You can include the full directory path or a relative directory path to the file name when specifying control_file, data_file, bad_file, discard_file, log_file, and param_file. If you specify the file name alone or with a relative directory path, the file is assumed to exist in the case of control_file, data_file, or param_file relative to the current working directory from which edbldr is invoked. In the case of bad_file, discard_file, or log_file, the file is created.

If you omit the -d option, the -p option, or the -h option, the defaults for the database, port, and host are determined by the same rules as other EDB Postgres Advanced Server utility programs, such as edb-psql.

Requirements

• The control file must exist in the character set encoding of the client where edbldr is invoked. If the client is in an encoding different from the database encoding, then you must set the PGCLIENTENCODING environment variable on the client to the client’s encoding prior to invoking edbldr. This technique ensures character set conversion between the client and the database server is done correctly.

• The file names must include these extensions:

  • control_file must use the .ctl extension.
  • data_file must use the .dat extension.
  • bad_file must use the .bad extension
  • discard_file must use the .dsc extension
  • log_file must include the .log extension

  If the provided file name doesn't have an extension, EDB*Loader assumes the actual file name includes the appropriate extension.

• The operating system account used to invoke edbldr must have read permission on the directories and files specified by control_file, data_file, and param_file.

• The operating system account enterprisedb must have write permission on the directories where bad_file, discard_file, and log_file are written.

Parameters

dbname

Name of the database containing the tables to load.

port

Port number on which the database server is accepting connections.

host

IP address of the host on which the database server is running.

USERID={ <username/password> | <username/> | <username> | / }

EDB*Loader connects to the database with <username>. <username> must be a superuser or a username with the required privileges. <password> is the password for <username>.

If you omit the USERID parameter, EDB*Loader prompts for username and password. If you specify USERID=username/, then EDB*Loader either:

• Uses the password file specified by the environment variable PGPASSFILE if PGPASSFILE is set

• Uses the .pgpass password file (pgpass.conf on Windows systems) if PGPASSFILE isn't set

  If you specify USERID=username, then EDB*Loader prompts for password. If you specify USERID=/, the connection is attempted using the operating system account as the user name.

  Note

  EDB*Loader ignores the EDB Postgres Advanced Server connection environment variables PGUSER and PGPASSWORD. See the PostgreSQL core documentation for information on the PGPASSFILE environment variable and the password file.

-c CONNECTION_STRING

connstr=CONNECTION_STRING

The -c or connstr= option allows you to specify all the connection parameters supported by libpq. With this option, you can also specify SSL connection parameters or other connection parameters supported by libpq. If you provide connection options such as -d, -h, -p, or userid=dbuser/dbpass separately, they might override the values provided by the -c or connstr= option.

CONTROL=control_file

control_file specifies the name of the control file containing EDB*Loader directives. If you don't specify a file extension, an extension of .ctl is assumed.

For more information on the control file, see Building the EDB*Loader control file.

DATA=data_file

data_file specifies the name of the file containing the data to load into the target table. If you don't specify a file extension, an extension of .dat is assumed. Specifying a data_file on the command line overrides the INFILE clause specified in the control file.

For more information about data_file, see Building the EDB*Loader control file.

BAD=bad_file

bad_file specifies the name of a file that receives input data records that can't be loaded due to errors. Specifying bad_file on the command line overrides any BADFILE clause specified in the control file.

For more information about bad_file, see Building the EDB*Loader control file.

DISCARD=discard_file

discard_file is the name of the file that receives input data records that don't meet any table’s selection criteria. Specifying discard_file on the command line overrides the DISCARDFILE clause in the control file.

For more information about discard_file, see Building the EDB*Loader control file.

DISCARDMAX=max_discard_recs

max_discard_recs is the maximum number of discarded records that can be encountered from the input data records before terminating the EDB*Loader session. Specifying max_discard_recs on the command line overrides the DISCARDMAX or DISCARDS clause in the control file.

For more information about max_discard_recs, see Building the EDB*Loader control file.

HANDLE_CONFLICTS={ FALSE | TRUE }

If any record insertion fails due to a unique constraint violation, EDB*Loader aborts the entire operation. You can instruct EDB*Loader to instead move the duplicate record to the BAD file and continue processing by setting HANDLE_CONFLICTS to TRUE. This behavior applies only if indexes are present. By default, HANDLE_CONFLICTS is set to FALSE.

Setting HANDLE_CONFLICTS to TRUE isn't supported with direct path loading. If you set this parameter to TRUE when direct path loading, EDB*Loader throws an error.

LOG=log_file

log_file specifies the name of the file in which EDB*Loader records the results of the EDB*Loader session.

If you omit the LOG parameter, EDB*Loader creates a log file with the name control_file_base.log in the directory from which edbldr is invoked. control_file_base is the base name of the control file used in the EDB*Loader session. The operating system account enterprisedb must have write permission on the directory where the log file is written.

PARFILE=param_file

param_file specifies the name of the file that contains command line parameters for the EDB*Loader session. You can specify command line parameters listed in this section in param_file instead of on the command line. Exceptions are the -d, -p, and -h options, and the PARFILE parameter.

Any parameter given in param_file overrides the same parameter supplied on the command line before the PARFILE option. Any parameter given on the command line that appears after the PARFILE option overrides the same parameter given in param_file.

!!! Note Unlike other EDB*Loader files, there's no default file name or extension assumed for param_file. However, by Oracle SQL*Loader convention, .par is typically used as an extension. It isn't required.

DIRECT= { FALSE | TRUE }

If DIRECT is set to TRUE, EDB*Loader performs a direct path load instead of a conventional path load. The default value of DIRECT is FALSE.

Don't set DIRECT=true when loading the data into a replicated table. If you're using EDB*Loader to load data into a replicated table and set DIRECT=true, indexes might omit rows that are in a table or might contain references to rows that were deleted. EnterpriseDB doesn't support direct inserts to load data into replicated tables.

For information about direct path loads, see Direct path load.

FREEZE= { FALSE | TRUE }

Set FREEZE to TRUE to copy the data with the rows frozen. A tuple guaranteed to be visible to all current and future transactions is marked as frozen to prevent transaction ID wraparound. For more information about frozen tuples, see the PostgreSQL core documentation.

You must specify a data-loading type of TRUNCATE in the control file when using the FREEZE option. FREEZE isn't supported for direct loading.

By default, FREEZE is FALSE.

ERRORS=error_count

error_count specifies the number of errors permitted before aborting the EDB*Loader session. The default is 50.

PARALLEL= { FALSE | TRUE }

Set PARALLEL to TRUE to indicate that this EDB*Loader session is one of a number of concurrent EDB*Loader sessions participating in a parallel direct path load. The default value of PARALLEL is FALSE.

When PARALLEL is TRUE, the DIRECT parameter must also be set to TRUE.

For more information about parallel direct path loads, see Parallel direct path load.

ROWS=n

n specifies the number of rows that EDB*Loader commits before loading the next set of n rows.

SKIP=skip_count

Number of records at the beginning of the input data file to skip before loading begins. The default is 0.

SKIP_INDEX_MAINTENANCE= { FALSE | TRUE }

If set to TRUE, index maintenance isn't performed as part of a direct path load, and indexes on the loaded table are marked as invalid. The default value of SKIP_INDEX_MAINTENANCE is FALSE.

During a parallel direct path load, target table indexes aren't updated. They're marked as invalid after the load is complete.

You can use the REINDEX command to rebuild an index. For more information about the REINDEX command, see the PostgreSQL core documentation.

edb_resource_group=group_name

group_name specifies the name of an EDB Resource Manager resource group to which to assign the EDB*Loader session.

Any default resource group that was assigned to the session is overridden by the resource group given by the edb_resource_group parameter specified on the edbldr command line. An example of such a group is a database user running the EDB*Loader session who was assigned a default resource group with the ALTER ROLE ... SET edb_resource_group command.

Examples

This example invokes EDB*Loader using a control file named emp.ctl to load a table in database edb. The file is located in the current working directory.

$ /usr/edb/as17/bin/edbldr -d edb USERID=enterprisedb/password
CONTROL=emp.ctl
EDB*Loader: Copyright (c) 2007-2021, EnterpriseDB Corporation.

Successfully loaded (4) records

In this example, EDB*Loader prompts for the user name and password since they're omitted from the command line. In addition, the files for the bad file and log file are specified with the BAD and LOG command line parameters.

$ /usr/edb/as17/bin/edbldr -d edb CONTROL=emp.ctl BAD=/tmp/emp.bad
LOG=/tmp/emp.log
Enter the user name : enterprisedb
Enter the password :
EDB*Loader: Copyright (c) 2007-2021, EnterpriseDB Corporation.

Successfully loaded (4) records

This example runs EDB*Loader using a parameter file located in the current working directory. The SKIP and ERRORS parameter default values are specified in the parameter file in addition the CONTROL, BAD, and LOG files. The parameter file, emp.par, contains:

CONTROL=emp.ctl
BAD=/tmp/emp.bad
LOG=/tmp/emp.log
SKIP=1
ERRORS=10

Invoke EDB*Loader with the parameter file:

$ /usr/edb/as17/bin/edbldr -d edb PARFILE=emp.par
Enter the user name : enterprisedb
Enter the password :
EDB*Loader: Copyright (c) 2007-2021, EnterpriseDB Corporation.

Successfully loaded (3) records

This example invokes EDB*Loader using a connstr= option that uses the emp.ctl control file located in the current working directory to load a table in a database named edb:

$ /usr/edb/as17/bin/edbldr connstr=\"sslmode=verify-ca sslcompression=0
host=127.0.0.1 dbname=edb port=5444 user=enterprisedb\" CONTROL=emp.ctl
EDB*Loader: Copyright (c) 2007-2021, EnterpriseDB Corporation.

Successfully loaded (4) records

This example invokes EDB*Loader using a normal user. For this example, one empty table bar is created and a normal user bob is created. The bob user is granted all privileges on the table bar. The CREATE TABLE command creates the empty table. The CREATE USER command creates the user, and the GRANT command gives required privileges to the user bob on the bar table:

CREATE TABLE bar(i int); 
CREATE USER bob identified by '123';
GRANT ALL on bar TO bob;

The control file and data file:

## Control file
EDBAS/ - (master) $ cat /tmp/edbldr.ctl 
LOAD DATA INFILE '/tmp/edbldr.dat'
truncate into table bar
(
i position(1:1)
)

## Data file
EDBAS/ - (master) $ cat /tmp/edbldr.dat 
1
2
3
5

Invoke EDB*Loader:

EDBAS/ - (master) $ /usr/edb/as17/bin/edbldr -d edb userid=bob/123 control=/tmp/edbldr.ctl
EDB*Loader: Copyright (c) 2007-2022, EnterpriseDB Corporation.

Successfully loaded (4) records

Exit codes

When EDB*Loader exits, it returns one of the following codes: