3

I have a Cloud SQL instance running PostgreSQL, and a .csv file on my local computer.

I created a user and a database. I logged in as that user, with that database and created a table that matches my .csv file.

I have a .csv file, and an import.sql file with the following:

COPY <my-table-name>
FROM 'C:\<path-to>\data.csv'
WITH (FORMAT csv);

When I run the psql command:

psql -f import.sql <connection string>

I get back:

psql:./import.sql:3: ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

I tried making my user a 'superuser' by doing it as the postgres user, but I get this:

psql:./add-superuser.sql:1: ERROR:  must be superuser to alter superusers

Showing all users, shows:

postgres-> \du
 cloudsqladmin     | Superuser, Create role, Create DB, Replication, Bypass RLS 
 cloudsqlagent     | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqlreplica   | Replication                                                | {}
 cloudsqlsuperuser | Create role, Create DB                                     | {}
 <my-user>         | Create role, Create DB                                     | {cloudsqlsuperuser}
 postgres          | Create role, Create DB                                     | {cloudsqlsuperuser}

postgres is a cloudsqlsuperuser, but not a 'Superuser'. How to I import this .csv file?

I don't know how to login/get access as the user cloudsqladmin, or to grant a role to allow importing a file. I desperately need help quickly.

Google's documentation in this one area of Cloud Sql, with Postgres is horrible. It sends me all over, and is very confusing.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
user10664542
  • 1,106
  • 1
  • 23
  • 43

3 Answers3

2

PostgreSQL is a managed service, therefore you have some limitations regarding the users. You can't grant SUPERUSER permissions. What you can do so far, is to import the CSV file from the console.

To import the file in the CloudSQL (PostgreSQL) database:

First make sure that the table exists in the database. Connect to your CloudSQl (PostgreSQL) instance. Run the command SELECT * FROM pg_catalog.pg_tables; to list all the tables. If you have found your table in the list, use the exact same name for Table when importing the data to the database.

  1. Upload the CSV file in a bucket folder

  2. Find the CloudSQL (PostgreSQL) instance in SQL page and click on the instance's name.

  3. In the Instance details page click on Import.

  4. In Cloud Storage file select the CSV file from bucket.

  5. In Format of import, select CSV

  6. In table write the name of the table you want to import the data in.

  7. Click on Import and the data will be imported.

I have tried the methods above myself and I uploaded successfully a custom CSV file to CloudSQL (PostgreSQL).

Andy
  • 17,423
  • 9
  • 52
  • 69
Andrei Cusnir
  • 2,735
  • 1
  • 14
  • 21
  • I have a requirement to script uploading a .csv to CloudSQL / PostgreSQL, and so the 'clicking' requirement short circuits using Postgres on GCP, therefore moving to another cloud provider seems like the best thing to do here. Google took a great database, one of the best open source databases out there, and dumbed it down, probably to be on par with their cloud database offerings. – user10664542 Jul 03 '19 at 18:55
1

got exactly the same problem yesterday. I needed to execute the following command :

COPY <my-table-name>
FROM STDIN
WITH (FORMAT csv);

and then copy-paste the content of my file in the console. Worked as a charm. Juste be sure to end your file by \. to close the input

Teocali
  • 2,725
  • 2
  • 23
  • 39
0

COPY won't do what you want anyway, because it reads a file from the server (the server having no access to files on the client machine).

You can use psql's \copy to do that, which uses COPY ... FROM STDIN behind the scenes.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I tried postgres==>\copy and that fails also. https://stackoverflow.com/questions/54297933/cannot-import-csv-into-cloud-sql-from-console – user10664542 Jan 21 '19 at 21:32
  • From Google support, it is not possible to upload a .csv file to Postgres running under Cloud SQL. They dumbed it down so that you could not, but you can with MySQL, but our technical requirements are not achievable using MySQL. – user10664542 Jun 29 '19 at 13:15
  • If your hosting provider limits you unduly, pick a better one. – Laurenz Albe Jun 30 '19 at 12:46
  • agreed, there is a way to do it (see answer below), but it's a very long process. I cannot script this. I have to fool around with Cloud Storage and click a bazillion times all over to get this to work. – user10664542 Jul 03 '19 at 18:53