CODEWITHSUNDEEP
postgresqlpsql
boulder_ruby
boulder_ruby

Reputation: 39763

psql telling me "database does not exist" in some places, recognizing it in others

I'm trying to run these commands from this tutorial (http://jamiecook.wordpress.com/2011/11/24/setting-up-postgresql-postgis-for-rails-on-ubuntu/)

sudo su - postgres
createdb -E UTF8 template_postgis # Create the template spatial database.
createlang -d template_postgis plpgsql # Adding PLPGSQL language support.
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
cat <<EOS | psql -d template_postgis
UPDATE sample_postgis_db SET datistemplate = TRUE WHERE datname = 'template_postgis';
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.geometry_columns TO PUBLIC;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.spatial_ref_sys TO PUBLIC;
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;
VACUUM FULL FREEZE;
EOS

and am getting the error: 

ERROR:  relation "sample_postgis_db" does not exist
LINE 1: UPDATE sample_postgis_db SET datistemplate=TRUE WHERE datnam...

But it certainly does exist:

psql -d postgres
\list
#=>
...
sample_postgis_db | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
...

I can login to it:

psql -d sample_postgis_db

but even when I run that UPDATE line (nonsensically of course, but its a good test I think) inside of the db's psql, it still doesn't recognize itself.

sample_postgis_db=# UPDATE sample_postgis_db SET datistemplate=TRUE WHERE datname = "template_postgis";
ERROR:  relation "sample_postgis_db" does not exist
LINE 1: UPDATE sample_postgis_db SET datistemplate=TRUE WHERE datnam...

Upvotes: 0

Views: 1683

Answers (2)

boulder_ruby
boulder_ruby

Reputation: 39763

I hate how mysterious this is...and maybe its just because I altered sample_postgis_db's role to SUPERUSER (out of desperation), but it just started working...

Upvotes: 0

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61696

The tutorial has:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';

that you replaced by this:

UPDATE sample_postgis_db SET datistemplate = TRUE WHERE datname = 'template_postgis';

This replacement is uncalled for, since what is updated here is a system table, not a database name.

Upvotes: 1

Related Questions