I was facing error while restoring the database dvdrental for postgres. Postgres 9 was installed on my machine and connect as postgres user with database postgres.
First i tried to execute restore.sql script using pgadmin sql executor and throw below error:
ERROR: syntax error at or near ''\''
LINE 35: \connect
SOLUTION
SQL need to execute on CMD, so open CMD and move to postgres installation directory
cd C:\Program Files\PostgreSQL\9.5\bin
>Enter
psql -h localhost -d postgres -U postgres -p 5432 -a -q -f C:\Users\userPostgres\Downloads\dvdrental\restore.sql
On Execution again faced error
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:920: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3057.dat" for reading: Permission denied
COPY public.actor (actor_id, first_name, last_name, last_update) FROM stdin;
COPY public.actor (actor_id, first_name, last_name, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3057.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:920: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3057.dat" for reading: Permission denied
--
-- Data for Name: address; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.address (address_id, address, address2, district, city_id, postal_code, phone, last_update) FROM stdin;
COPY public.address (address_id, address, address2, district, city_id, postal_code, phone, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3065.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:928: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3065.dat" for reading: Permission denied
--
-- Data for Name: category; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.category (category_id, name, last_update) FROM stdin;
COPY public.category (category_id, name, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3059.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:936: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3059.dat" for reading: Permission denied
--
-- Data for Name: city; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.city (city_id, city, country_id, last_update) FROM stdin;
COPY public.city (city_id, city, country_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3067.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:944: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3067.dat" for reading: Permission denied
--
-- Data for Name: country; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.country (country_id, country, last_update) FROM stdin;
COPY public.country (country_id, country, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3069.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:952: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3069.dat" for reading: Permission denied
--
-- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.customer (customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active) FROM stdin;
COPY public.customer (customer_id, store_id, first_name, last_name, email, address_id, activebool, create_date, last_update, active) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3055.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:960: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3055.dat" for reading: Permission denied
--
-- Data for Name: film; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.film (film_id, title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, last_update, special_features, fulltext) FROM stdin;
COPY public.film (film_id, title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, last_update, special_features, fulltext) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3061.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:968: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3061.dat" for reading: Permission denied
--
-- Data for Name: film_actor; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.film_actor (actor_id, film_id, last_update) FROM stdin;
COPY public.film_actor (actor_id, film_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3062.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:976: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3062.dat" for reading: Permission denied
--
-- Data for Name: film_category; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.film_category (film_id, category_id, last_update) FROM stdin;
COPY public.film_category (film_id, category_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3063.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:984: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3063.dat" for reading: Permission denied
--
-- Data for Name: inventory; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.inventory (inventory_id, film_id, store_id, last_update) FROM stdin;
COPY public.inventory (inventory_id, film_id, store_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3071.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:992: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3071.dat" for reading: Permission denied
--
-- Data for Name: language; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.language (language_id, name, last_update) FROM stdin;
COPY public.language (language_id, name, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3073.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1000: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3073.dat" for reading: Permission denied
--
-- Data for Name: payment; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date) FROM stdin;
COPY public.payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3075.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1008: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3075.dat" for reading: Permission denied
--
-- Data for Name: rental; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update) FROM stdin;
COPY public.rental (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3077.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1016: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3077.dat" for reading: Permission denied
--
-- Data for Name: staff; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.staff (staff_id, first_name, last_name, address_id, email, store_id, active, username, password, last_update, picture) FROM stdin;
COPY public.staff (staff_id, first_name, last_name, address_id, email, store_id, active, username, password, last_update, picture) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3079.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1024: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3079.dat" for reading: Permission denied
--
-- Data for Name: store; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.store (store_id, manager_staff_id, address_id, last_update) FROM stdin;
COPY public.store (store_id, manager_staff_id, address_id, last_update) FROM 'C:\Users\userPostgres\Downloads\dvdrental\3081.dat';
psql:C:/Users/userPostgres/Downloads/dvdrental/restore.sql:1032: ERROR: could not open file "C:\Users\userPostgres\Downloads\dvdrental\3081.dat" for reading: Permission denied
SOLUTION
1. Check Directory permission (read,write) to window user
2. Change "dvdrental" directory to some another drive, i moved folder to E drive, now path is as below, Also dont forget to change the path within the restore.sql file
psql -h localhost -d postgres -U postgres -p 5432 -a -q -f E\dvdrental\restore.sql
No comments:
Post a Comment