If you’re client computer is a Windows system you may need to include an encoding option ( UTF8) with the \copy command when importing the GeoLite2-City-Locations-en.csv file to avoid encoding compatibility errors: psql -U test -d dbname -h hostname -p port -c "\copy geoip_locations FROM 'C:\tmp\GeoLite2-City-Locations-en.csv' with (format csv,header true, delimiter ',', encoding 'UTF8') " Like the ‘HINT’ states, psql’s \copy meta command can be used to copy the files from your client computer to your database: psql -U test -d dbname -h hostname -p port -c "\copy geoip_blocks FROM 'C:\tmp\GeoLite2-Country-Blocks-IPv4.csv' with (format csv,header true, delimiter ',') " In this case the simplest option is to run the CREATE statements and copy commands independently, but in order. psql's \copy command also works for anyone. HINT: Anyone can COPY to stdout or from stdin. You may encounter the following error (particularly if connecting to a remote database such as AWS RDS or Google Cloud SQL via a client/psql using a non-superuser account): ERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file Psql's \copy meta command to import CSV files to database */Ĭopy geoip_blocks from '/tmp/GeoLite2-City-Blocks-IPv4.csv' delimiter ',' csv header Ĭopy geoip_blocks from '/tmp/GeoLite2-City-Blocks-IPv6.csv' delimiter ',' csv header ĬREATE INDEX geoip_blocks_network_idx ON geoip_blocks USING gist (network inet_ops) Īutonomous_system_organization varchar(255)Ĭopy asn_blocks from '/tmp/GeoLite2-ASN-Blocks-IPv4.csv' delimiter ',' csv header Ĭopy asn_blocks from '/tmp/GeoLite2-ASN-Blocks-IPv6.csv' delimiter ',' csv header ĬREATE INDEX asn_blocks_network_idx ON asn_blocks USING gist (network inet_ops) Ĭopy geoip_locations from '/tmp/GeoLite2-City-Locations-en.csv' delimiter ',' csv header encoding 'UTF8' * If user is not a superuser or member of the pg_read_server_files role use Note: If you don’t require the granularity or resolution of city names or the ASN database you can import only the country geoip blocks and locations by running the statements in the Import GeoLite2’s country network blocks and locations DataGrip, DBeaver) and run the following SQL statements to create the necessary tables and import the Geolite2 (City & ASN) data from the CSV files you extracted in Step 1: Import GeoLite2’s city and ASN network blocks and city locations Login to your PostgreSQL database using psql or your preferred database IDE (e.g. tmp/ Step 2 - Import GeoLite2 CSV files to PostgreSQL
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |