I am trying tap-postgres to target-redshift. But am getting this error when writing data to redshift. I am reading from tap-postgres. tap-Postgres to target-csv is working fine.
tap-postgres -> conf.json
{
"host": "localhost",
"port": 5432,
"dbname": "singer",
"user": "postgres",
"password": "password",
"schema": "public"
}
tap-catalog.json
{
"streams": [
{
"table_name": "student",
"stream": "singer-postgres",
"metadata": [
{
"breadcrumb": [],
"metadata": {
"table-key-properties": [
"id"
],
"selected": true,
"replication-method": "FULL_TABLE",
"schema-name": "public",
"database-name": "singer",
"row-count": 9,
"is-view": false
}
},
{
"breadcrumb": [
"properties",
"id"
],
"metadata": {
"sql-datatype": "integer",
"inclusion": "automatic",
"selected-by-default": true,
"selected": true
}
},
{
"breadcrumb": [
"properties",
"name"
],
"metadata": {
"sql-datatype": "text",
"inclusion": "available",
"selected-by-default": true,
"selected": true
}
},
{
"breadcrumb": [
"properties",
"email"
],
"metadata": {
"sql-datatype": "text",
"inclusion": "available",
"selected-by-default": true,
"selected": true
}
}
],
"tap_stream_id": "singer-public-demo",
"schema": {
"type": "object",
"properties": {
"id": {
"type": [
"integer"
],
"minimum": -2147483648,
"maximum": 2147483647,
"selected": true
},
"name": {
"type": [
"null",
"string"
],
"maxLength": 80,
"selected": true
},
"email": {
"type": [
"null",
"string"
],
"maxLength": 80,
"selected": true
}
},
"definitions": {
"sdc_recursive_integer_array": {
"type": [
"null",
"integer",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_integer_array"
}
},
"sdc_recursive_number_array": {
"type": [
"null",
"number",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_number_array"
}
},
"sdc_recursive_string_array": {
"type": [
"null",
"string",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_string_array"
}
},
"sdc_recursive_boolean_array": {
"type": [
"null",
"boolean",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_boolean_array"
}
},
"sdc_recursive_timestamp_array": {
"type": [
"null",
"string",
"array"
],
"format": "date-time",
"items": {
"$ref": "#/definitions/sdc_recursive_timestamp_array"
}
},
"sdc_recursive_object_array": {
"type": [
"null",
"object",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_object_array"
}
}
}
}
}
]
}
target_redshift_config.json
{
"redshift_host": "host_name",
"redshift_port": 5439,
"redshift_database": "database_name",
"redshift_username": "user_name",
"redshift_password": "password",
"redshift_schema": "schema_name",
"Trusted_Connection": "True",
"default_column_length": 1000,
"target_s3": {
"aws_access_key_id": "AXXXXXXXXXXXXXXXXXXXXXX",
"aws_secret_access_key": "xxxxxxxxxxxxxxxxx",
"bucket": "bigdata/phani",
"key_prefix": "_tmp"
},
"schema":{
"properties":{
"id":{
"inclusion":"available",
"type":[
"integer"
]
},
"name":{
"inclusion":"available",
"type":[
"string"
]
},
"email":{
"inclusion":"available",
"type":[
"string"
]
}
}
}
}
The error screenshot is below. Was looking for inputs on the below. Can you help on how to resolve the below error
phani@ubuntu:~$ ~/.virtualenvs/tap-postgres/bin/tap-postgres -c /home/phani/tap-postgres/conf.json --properties /home/phani/tap-postgres/tap-catalog.json | ~/.virtualenvs/target-redshift/bin/target-redshift --config /home/phani/target-redshift/target_redshift_config.json > state.json
/home/phani/.virtualenvs/tap-postgres/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
""")
INFO Failed to retrieve SSL status
INFO Selected streams: ['singer-public-demo']
INFO No currently_syncing found
INFO Beginning sync of stream(singer-public-demo) with sync method(full)
INFO Stream singer-public-demo is using full_table replication
INFO Current Server Encoding: UTF8
INFO Current Client Encoding: UTF8
INFO hstore is UNavailable
INFO Beginning new Full Table replication 1600403309215
INFO select SELECT "email" , "id" , "name" , xmin::text::bigint
FROM "public"."student"
ORDER BY xmin::text::bigint ASC with itersize 20000
INFO METRIC: {"type": "counter", "metric": "record_count", "value": 9, "tags": {}}
Traceback (most recent call last):
File "/home/phani/.virtualenvs/target-redshift/bin/target-redshift", line 8, in <module>
sys.exit(cli())
File "/home/phani/.virtualenvs/target-redshift/lib/python3.6/site-packages/target_redshift/__init__.py", line 55, in cli
main(args.config)
File "/home/phani/.virtualenvs/target-redshift/lib/python3.6/site-packages/target_redshift/__init__.py", line 28, in main
password=config.get('redshift_password')
File "/home/phani/.virtualenvs/target-redshift/lib/python3.6/site-packages/psycopg2/__init__.py", line 127, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "host_url" (ip) and accepting
TCP/IP connections on port 5439?
Your stack trace tells you that it is not able to connect to Redshift
I would double check your connection details in
target_redshift_config.jsonand make sure that you can connect to that DB from your IP. AWS loves being ultra-secure, sometimes to a point of painfulness, so it's possible your IP is restricted from accessing that Redshift instance. If that's the case you'll need to update the security policy on your Redshift DB.