Running EDB*Loader v15
Use the following command to invoke EDB*Loader from the command line:
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
extensiondiscard_file
must use the.dsc
extensionlog_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
, andparam_file
.The operating system account enterprisedb must have write permission on the directories where
bad_file
,discard_file
, andlog_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
ifPGPASSFILE
is setUses the
.pgpass
password file (pgpass.conf
on Windows systems) ifPGPASSFILE
isn't setIf you specify
USERID=username
, then EDB*Loader prompts forpassword
. If you specifyUSERID=/
, 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
andPGPASSWORD
. See the PostgreSQL core documentation for information on thePGPASSFILE
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.
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.
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:
Invoke EDB*Loader with the parameter file:
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
:
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:
The control file and data file:
Invoke EDB*Loader:
Exit codes
When EDB*Loader exits, it returns one of the following codes:
Exit code | Description |
---|---|
0 | All rows loaded successfully. |
1 | EDB*Loader encountered command line or syntax errors or aborted the load operation due to an unrecoverable error. |
2 | The load completed, but some or all rows were rejected or discarded. |
3 | EDB*Loader encountered fatal errors, such as OS errors. This class of errors is equivalent to the FATAL or PANIC severity levels of PostgreSQL errors. |