Comments (16)
@melaraj2 Thanks for opening the issue. Can you try deleting the database and running this again + making sure you use the latest release. I'm not able to reproduce this.
from cloudquery.
sure here is the full shell session
manuelelaraj@Manuels-MacBook-Pro cloudquery % curl -L https://github.com/cloudquery/cloudquery/releases/latest/download/cloudquery_${OS}_x86_64 -o cloudquery
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 156 100 156 0 0 647 0 --:--:-- --:--:-- --:--:-- 647
100 650 100 650 0 0 1692 0 --:--:-- --:--:-- --:--:-- 634k
100 81.2M 100 81.2M 0 0 5507k 0 0:00:15 0:00:15 --:--:-- 3922k
manuelelaraj@Manuels-MacBook-Pro cloudquery % ./cloudquery fetch --driver postgresql --dsn "host=proddb.pocnettech.com port=5432 user=melaraj password=MaherManuco99 dbname=awsdb" -v
2020-12-27T12:01:12.726-0500 INFO aws/provider.go:136 Creating tables if needed {"provider": "aws"}
2020/12/27 12:01:36 /go/src/github.com/troian/golang-cross-example/providers/aws/ec2/vpc_peering_connections.go:193 ERROR: column "requester_option_allow_egress_from_local_classic_link_to_remote" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
[155.088ms] [rows:0] ALTER TABLE "aws_ec2_vpc_peering_connections" ADD "requester_option_allow_egress_from_local_classic_link_to_remote_vpc" boolean
Error: ERROR: column "requester_option_allow_egress_from_local_classic_link_to_remote" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
Usage:
cloudquery fetch [flags]
Flags:
--driver string database driver sqlite/postgresql/mysql/sqlserver (default "sqlite")
--dsn string database connection string or filepath if driver is sqlite (default "./cloudquery.db")
-h, --help help for fetch
--path string path to configuration file. can be generated with 'gen config' command (default "./config.yml")
-v, --verbose verbose output
--version version for fetch
2020/12/27 12:01:36 ERROR: column "requester_option_allow_egress_from_local_classic_link_to_remote" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
manuelelaraj@Manuels-MacBook-Pro cloudquery %
from cloudquery.
can you try dropping the postgress database before running cloudquery fetch
just to be sure that you start from a clean state. Also you might accidentally exposed the username/password of your postgres instance (If yes, I suggest you change it).
from cloudquery.
I did that (Dropped all tables and sequences), I ran the first time and it works. I am now getting rate exceeded, but I have already opened a ticket with AWS on that. So ignore that bit.
On the second call, I am getting the same error:
manuelelaraj@Manuels-MacBook-Pro cloudquery % ./cloudquery fetch --driver postgresql --dsn "host=proddb.pocnettech.com port=5432 user=melaraj password=MaherManuco99 dbname=awsdb" -v
2020-12-27T12:40:35.235-0500 INFO aws/provider.go:136 Creating tables if needed {"provider": "aws"}
2020/12/27 12:40:59 /go/src/github.com/troian/golang-cross-example/providers/aws/ec2/vpc_peering_connections.go:193 ERROR: column "accepter_option_allow_egress_from_local_classic_link_to_remote_" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
[161.189ms] [rows:0] ALTER TABLE "aws_ec2_vpc_peering_connections" ADD "accepter_option_allow_egress_from_local_classic_link_to_remote_vpc" boolean
Error: ERROR: column "accepter_option_allow_egress_from_local_classic_link_to_remote_" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
Usage:
cloudquery fetch [flags]
Flags:
--driver string database driver sqlite/postgresql/mysql/sqlserver (default "sqlite")
--dsn string database connection string or filepath if driver is sqlite (default "./cloudquery.db")
-h, --help help for fetch
--path string path to configuration file. can be generated with 'gen config' command (default "./config.yml")
-v, --verbose verbose output
--version version for fetch
2020/12/27 12:40:59 ERROR: column "accepter_option_allow_egress_from_local_classic_link_to_remote_" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
from cloudquery.
Thank you for pointing out about the password. its been changed, althouth the instance in a private network.
Here is a full set of command that you could try to reproduce if you have docker installed.
manuelelaraj@Manuels-MacBook-Pro cloudquery % curl -L https://github.com/cloudquery/cloudquery/releases/latest/download/cloudquery_${OS}_x86_64 -o cloudquery
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 156 100 156 0 0 520 0 --:--:-- --:--:-- --:--:-- 520
100 650 100 650 0 0 1385 0 --:--:-- --:--:-- --:--:-- 1385
100 81.2M 100 81.2M 0 0 6202k 0 0:00:13 0:00:13 --:--:-- 6320k
manuelelaraj@Manuels-MacBook-Pro cloudquery % docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=secretpass -d postgres:12-alpine
0f0e926a38733b06cf68d5e1472061489d022d594a7d339985d9c64f3c1bbdf6
manuelelaraj@Manuels-MacBook-Pro cloudquery % ./cloudquery fetch --driver postgresql --dsn "host=localhost port=5432 user=postgres password=secretpass dbname=postgres" -v
2020-12-27T18:56:14.651-0500 INFO aws/provider.go:136 Creating tables if needed {"provider": "aws"}
2020-12-27T18:56:16.347-0500 INFO iam/groups.go:66 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.groups", "count": 0}
2020-12-27T18:56:16.370-0500 INFO autoscaling/launch_configurations.go:194 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "auto_scaling.launch_configurations", "count": 0}
2020-12-27T18:56:16.373-0500 INFO iam/password_policies.go:69 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.password_policies", "count": 1}
2020-12-27T18:56:16.388-0500 INFO efs/filesystems.go:115 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "efs.filesystems", "count": 1}
2020-12-27T18:56:16.396-0500 INFO iam/roles.go:105 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.roles", "count": 100}
2020-12-27T18:56:16.416-0500 INFO cloudtrail/trails.go:89 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "cloudtrail.trails", "count": 1}
2020-12-27T18:56:16.422-0500 INFO elasticbeanstalk/enironments.go:200 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "elasticbeanstalk.environments", "count": 0}
2020-12-27T18:56:16.429-0500 INFO redshift/clusters.go:374 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "redshift.clusters", "count": 0}
2020-12-27T18:56:16.431-0500 INFO redshift/subnet_groups.go:151 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "redshift.cluster_subnet_groups", "count": 0}
2020-12-27T18:56:16.438-0500 INFO rds/subnet_groups.go:100 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "rds.subnet_groups", "count": 0}
2020-12-27T18:56:16.438-0500 INFO emr/clusters.go:90 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "emr.clusters", "count": 0}
2020-12-27T18:56:16.453-0500 INFO ec2/customer_gateways.go:98 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.customer_gateways", "count": 0}
2020-12-27T18:56:16.470-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:16.474-0500 INFO directconnect/gateways.go:69 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "directconnect.gateways", "count": 0}
2020-12-27T18:56:16.481-0500 INFO rds/clusters.go:311 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "rds.clusters", "count": 0}
2020-12-27T18:56:16.490-0500 INFO ec2/internet_gateways.go:119 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "count": 1}
2020-12-27T18:56:16.505-0500 INFO rds/certificates.go:74 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "rds.certificates", "count": 1}
2020-12-27T18:56:16.513-0500 INFO ec2/images.go:196 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.images", "count": 0}
2020-12-27T18:56:16.525-0500 INFO fsx/backups.go:105 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "fsx.backups", "count": 0}
2020-12-27T18:56:16.541-0500 INFO ec2/flow_logs.go:113 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.flow_logs", "count": 0}
2020-12-27T18:56:16.541-0500 INFO ec2/network_acls.go:166 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.network_acls", "count": 1}
2020-12-27T18:56:16.544-0500 INFO ec2/subnets.go:147 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.subnets", "count": 6}
2020-12-27T18:56:16.551-0500 INFO ec2/vpcs.go:166 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.vpcs", "count": 1}
2020-12-27T18:56:16.560-0500 INFO ec2/route_tabls.go:211 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.route_tables", "count": 2}
2020-12-27T18:56:16.562-0500 INFO ec2/nat_gateways.go:138 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.nat_gateways", "count": 1}
2020-12-27T18:56:16.579-0500 INFO elbv2/load_balancers.go:144 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "elbv2.load_balancers", "count": 3}
2020-12-27T18:56:16.585-0500 INFO iam/roles.go:105 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.roles", "count": 70}
2020-12-27T18:56:16.585-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:16.592-0500 INFO ec2/instances.go:475 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.instances", "count": 1}
2020-12-27T18:56:16.595-0500 INFO ec2/vpc_peering_connections.go:217 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.vpc_peering_connections", "count": 2}
2020-12-27T18:56:16.607-0500 INFO iam/users.go:243 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.users", "count": 1}
2020-12-27T18:56:16.648-0500 INFO ecs/clusters.go:198 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ecs.cluster", "count": 1}
2020-12-27T18:56:16.654-0500 INFO ec2/security_groups.go:253 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "ec2.security_groups", "count": 59}
2020-12-27T18:56:16.686-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:16.777-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:16.869-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:16.959-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:17.047-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 100}
2020-12-27T18:56:17.069-0500 INFO kms/keys.go:149 Fetched resources {"provider": "aws", "account_id": "386128822572", "region": "us-east-1", "resource": "kms.keys", "count": 11}
2020-12-27T18:56:17.135-0500 INFO iam/policies.go:78 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "iam.policies", "count": 94}
2020-12-27T18:56:19.575-0500 INFO s3/buckets.go:257 Fetched resources {"provider": "aws", "account_id": "386128822572", "resource": "s3.buckets", "count": 8}
manuelelaraj@Manuels-MacBook-Pro cloudquery % echo "SECOND RUN"
SECOND RUN
manuelelaraj@Manuels-MacBook-Pro cloudquery % ./cloudquery fetch --driver postgresql --dsn "host=localhost port=5432 user=postgres password=secretpass dbname=postgres" -v
2020-12-27T18:56:45.394-0500 INFO aws/provider.go:136 Creating tables if needed {"provider": "aws"}
2020/12/27 18:56:45 /go/src/github.com/troian/golang-cross-example/providers/aws/ec2/vpc_peering_connections.go:193 ERROR: column "accepter_option_allow_egress_from_local_vpc_to_remote_classic_l" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
[1.814ms] [rows:0] ALTER TABLE "aws_ec2_vpc_peering_connections" ADD "accepter_option_allow_egress_from_local_vpc_to_remote_classic_link" boolean
Error: ERROR: column "accepter_option_allow_egress_from_local_vpc_to_remote_classic_l" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
Usage:
cloudquery fetch [flags]
Flags:
--driver string database driver sqlite/postgresql/mysql/sqlserver (default "sqlite")
--dsn string database connection string or filepath if driver is sqlite (default "./cloudquery.db")
-h, --help help for fetch
--path string path to configuration file. can be generated with 'gen config' command (default "./config.yml")
-v, --verbose verbose output
--version version for fetch
2020/12/27 18:56:45 ERROR: column "accepter_option_allow_egress_from_local_vpc_to_remote_classic_l" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
manuelelaraj@Manuels-MacBook-Pro cloudquery %
from cloudquery.
I've updated the gorm
version to latest can you try Again? Also, can you please share the exact version of your postgresql server?
from cloudquery.
I am still seeing the same issue.
I have used Postgresql 9.6.17 and, locally I have been testing with Postgresql 12.5. I get the same exact error each time:
The below is with Pg 12.5
manuelelaraj@Manuels-MacBook-Pro cloudquery % ./cloudquery fetch --driver postgresql --dsn "host=localhost port=5432 user=postgres password=secretpass dbname=postgres" -v
2020-12-28T15:31:16.209-0500 INFO aws/provider.go:136 Creating tables if needed {"provider": "aws"}
2020/12/28 15:31:16 /go/src/github.com/troian/golang-cross-example/providers/aws/ec2/vpc_peering_connections.go:193 ERROR: column "requester_option_allow_egress_from_local_vpc_to_remote_classic_" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
[5.683ms] [rows:0] ALTER TABLE "aws_ec2_vpc_peering_connections" ADD "requester_option_allow_egress_from_local_vpc_to_remote_classic_link" boolean
Error: ERROR: column "requester_option_allow_egress_from_local_vpc_to_remote_classic_" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
Usage:
cloudquery fetch [flags]
Flags:
--driver string database driver sqlite/postgresql/mysql/sqlserver (default "sqlite")
--dsn string database connection string or filepath if driver is sqlite (default "./cloudquery.db")
-h, --help help for fetch
--path string path to configuration file. can be generated with 'gen config' command (default "./config.yml")
-v, --verbose verbose output
--version version for fetch
2020/12/28 15:31:16 ERROR: column "requester_option_allow_egress_from_local_vpc_to_remote_classic_" of relation "aws_ec2_vpc_peering_connections" already exists (SQLSTATE 42701)
manuelelaraj@Manuels-MacBook-Pro cloudquery % ./cloudquery --version
cloudquery version 0.6.4
from cloudquery.
@melaraj2 I believe I fixed the bug in v0.6.5. It was due to columns having longer name than supported by Postgresql.
from cloudquery.
I have to disagree.
I dropped the database and recreated it. I ran the first time fine, and the second time, I am still seeing the same issue:
2020/12/29 04:20:15 /go/src/github.com/troian/golang-cross-example/providers/aws/ec2/vpc_peering_connections.go:193 SLOW SQL >= 200ms
[237.517ms] [rows:-] SELECT * FROM "aws_ec2_vpc_peering_connection_requester_ipv6_cidr_blocks" LIMIT 1
2020/12/29 04:20:15 /go/src/github.com/troian/golang-cross-example/providers/aws/ec2/vpc_peering_connections.go:193 ERROR: constraint "fk_aws_ec2_vpc_peering_connections_requester_ipv6_cidr_block_se" for relation "aws_ec2_vpc_peering_connection_requester_ipv6_cidr_blocks" already exists (SQLSTATE 42710)
[161.569ms] [rows:0] ALTER TABLE "aws_ec2_vpc_peering_connection_requester_ipv6_cidr_blocks" ADD CONSTRAINT "fk_aws_ec2_vpc_peering_connections_requester_ipv6_cidr_block_set" FOREIGN KEY ("vpc_peering_connection_id") REFERENCES "aws_ec2_vpc_peering_connections"("id") ON DELETE CASCADE
Error: ERROR: constraint "fk_aws_ec2_vpc_peering_connections_requester_ipv6_cidr_block_se" for relation "aws_ec2_vpc_peering_connection_requester_ipv6_cidr_blocks" already exists (SQLSTATE 42710)
Usage:
cloudquery fetch [flags]
Flags:
--driver string database driver sqlite/postgresql/mysql/sqlserver (default "sqlite")
--dsn string database connection string or filepath if driver is sqlite (default "./cloudquery.db")
-h, --help help for fetch
--path string path to configuration file. can be generated with 'gen config' command (default "./config.yml")
-v, --verbose verbose output
--version version for fetch
2020/12/29 04:20:15 ERROR: constraint "fk_aws_ec2_vpc_peering_connections_requester_ipv6_cidr_block_se" for relation "aws_ec2_vpc_peering_connection_requester_ipv6_cidr_blocks" already exists (SQLSTATE 42710)
manuelelaraj@Manuels-MacBook-Pro cloudquery %
from cloudquery.
Well, actually it is the same issue, but a different database object.
from cloudquery.
You are correct. I've tested it this time on postgresql 9.6.17 and 12.5 hopefully v0.6.6 fixes it. Can you please test it? Thanks for your patience!
from cloudquery.
Indeed it works now!
I have a quick question. How do you deal with when a resource changed from the last time it was collected? Do you just update the record?
from cloudquery.
when a specific resource is collected say ec2_instances
for a specific account. First all instances
for this specific account are deleted and the new one are inserted.
from cloudquery.
It would be great if there was a changelog table. It can be done in an automated way
it would look something like this.
resource_id | resource_type | changes | timestamp |
---|---|---|---|
i-0ee9bce71f39190d4 | ec2_instance | [{"field_changed":"instance_type","previous_value":"t2.large","new_value":"t3.large"}] | 2020-12-29 4:30 |
from cloudquery.
If you are interested, I will open a new issue, this could be done in a separate function using reflection basically you pass the new record and old record, and the function analyzes the differences and insert the changelog record. I am mostly a Java guy, but in Go, there must be "reflections" equivalent to pull this off without knowing the structure of each record.
from cloudquery.
Yes, definitely interested. Please open an issue. Thx!
from cloudquery.
Related Issues (20)
- feat: Add project and group settings to gitlab source plugin
- feat: Allow `aws_organizations_accounts` to be used as a cache to retrieve account IDs to sync from
- bug: Failed to save state: write failed: table cq_state_bq not found HOT 2
- feat: Support impersonate service account in the BigQuery destination plugin HOT 1
- feat (resources): add Oracle Kubernetes Clusters (OKE) table and child tables
- bug: Docker plugins from CloudQuery registry don't work with team API keys
- fix: Tests not Reporting Failures Properly
- bug: Inconsistant Tag Structure in AWS Plugin HOT 1
- feat: S3 Path Based on Data
- bug: For GCS plugin tests, the bucket name needs to be set in file HOT 1
- feat: Add CloudFront Key Value Stores to AWS source plugin
- feat: Add Snyk Targets HOT 1
- bug: Snyk Rate Limiting
- bug: S3 ContentType
- Visibility AWS ENIs usage per aws service
- bug: Running Sequential Syncs with MySQL results in error
- bug: Build: `CGO_ENABLED` doesn't seem to have an effect in the release process HOT 1
- feat: Document a "rich" docker image is needed to run the SQLite, DuckDB and Snowflake plugins
- Feedback for Official Stripe Plugin. HOT 12
- feat: Support more Reserved Instances tables HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from cloudquery.