Org.postgresql.util.psqlexception Error Language Plpgsql Already Exists
Contents |
Nothing For PostgreSQL 9.0 and newer In current releases of PostgreSQL "CREATE OR REPLACE LANGUAGE" postgresql create or replace table is the native syntax for installing a procedural language with no postgresql language plpgsql does not exist error if it's already installed. Also PL/pgSQL is installed in the template databases at install time, error: must be owner of extension plpgsql rds so will be included in all newly created databases by default, so there's usually no need to install it in schema installation scripts. For PostgreSQL 8.4
Must Be Owner Of Language Plpgsql
and older While there is no CREATE OR REPLACE for languages like there are for functions, you can simulate one for the common case where you want to add the pl/pgsql language to a database. Normally this will trigger an ERROR condition if the language is already installed: $ psql -c "CREATE LANGUAGE create extension if not exists plpgsql with schema pg_catalog plpgsql" ERROR: language "plpgsql" already exists But the following snippet will add the language only if it doesn't already exist: CREATE OR REPLACE FUNCTION make_plpgsql() RETURNS VOID LANGUAGE SQL AS $$ CREATE LANGUAGE plpgsql; $$; SELECT CASE WHEN EXISTS( SELECT 1 FROM pg_catalog.pg_language WHERE lanname='plpgsql' ) THEN NULL ELSE make_plpgsql() END; DROP FUNCTION make_plpgsql(); You can run this multiple times and it will never produce the error shown above. The DROP FUNCTION at the end is optional, if you want to re-use this snippet in other code you might keep the function around to be referenced later. Original code by David Fetter Retrieved from "https://wiki.postgresql.org/index.php?title=CREATE_OR_REPLACE_LANGUAGE&oldid=22880" Category: Snippets Views Page Discussion View source History Personal tools Log in Navigation Main Page Random page Recent changes Help Search Tools What links here Related changes Special pages Printable version Permanent link This page was last modified on 15 July 2014, at 00:11. Privacy policy About PostgreSQL wiki Disclaimers
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists plpgsql extension IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly
Pg_restore Must Be Owner Of Extension Plpgsql
News PostgreSQL v9.0.3 Database Import Error: language "plpgsql" already exists From: "Gnanakumar"
Heroku Error: Must Be Owner Of Extension Plpgsql
PostgreSQL v9.0.3 Database Import Error: language "plpgsql" already exists Date: 2011-03-30 13:44:33 Message-ID: 007201cbeee0$9c9d9890$d5d8c9b0$@com (view raw or whole thread) Thread: 2011-03-30 13:44:33 from "Gnanakumar"
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more http://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping https://wiki.collectionspace.org/display/DOC/Troubleshooting+Installation+Problems each other. Join them; it only takes a minute: Sign up How to solve privileges issues when restore PostgreSQL Database up vote 37 down vote favorite 7 I have dumped a clean, no owner backup for Postgres Database must be with the command pg_dump sample_database -O -c -U Later, when I restore the database with psql -d sample_database -U app_name However, I encountered several errors which prevents me from restoring the data: ERROR: must be owner of extension plpgsql ERROR: must be owner of schema public ERROR: schema "public" already exists ERROR: must be owner of schema public CREATE EXTENSION ERROR: must be owner of extension plpgsql I digged into the plain-text SQL pg_dump generates and I must be owner found it contains SQL CREATE SCHEMA public; COMMENT ON SCHEMA public IS 'standard public schema'; CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; I think the causes are that the user app_name doesn't have the privileges to alter the public schema and plpgsql. How could I solve this issue? postgresql database-backups rails-postgresql share|improve this question asked Nov 16 '12 at 4:48 steveyang 4,15543762 If you don't need plpgsql, then DROP EXTENSION plpgsql before you pg_dump. This is safer than making your app a super user, and it is more convenient than ignoring errors (which bombs if you use --single-transaction or -v ON_ERROR_STOP=1). This is a known issue, [discussed at length by the Postgres developers|postgresql.org/message-id/… but not fixed as of 9.3. –mehaase Jun 5 '15 at 21:16 add a comment| 4 Answers 4 active oldest votes up vote 29 down vote To solve the issue you must assign the proper ownership permissions. Try the below which should resolve all permission related issues for specific users but as stated in the comments this should not be used in production: root@server:/var/log/postgresql# sudo -u postgres psql psql (8.4.4) Type "help" for help. postgres=# \du List of roles Role name | Attributes | Member of -----------------+-------------+-----------
Page History Restrictions Page Information Link to this Pageā¦ View in Hierarchy View Source Export to PDF Export to Word CollectionSpace Current Documentation … CollectionSpace Release Documentation Installing CollectionSpace Installation-related Topics Troubleshooting Installation Problems Skip to end of metadata Created by Penelope Madry on Oct 10, 2014 Go to start of metadata Troubleshooting installation problems Memory errors when running Ant (ant) or Maven (mvn) Errors while running Ant, where "build.xml" appears in any relevant error message Verifying that the installation was successful Issues with user account permissions, even though all the steps of the install instructions were successful 'Directory does not exist' error during ant undeploy Deployment errors due to restrictive filesystem permissions Port conflicts starting up the CollectionSpace servers Verifying that the databases were created successfully PostgreSQL MySQL Options missing from some dropdown menus Error creating authority term records (Person, Organization, Storage Location ...) Troubleshooting issues in setting up CollectionSpace to use the PostgreSQL database. Selected services functionality is failing: requesting lists, searching, sorting (ordering) 'new encoding (UTF8) is incompatible' error 'no pg_hba.conf entry for host "::1"' errors 'language "plpgsql" does not exist' errors 'HeuristicMixedException' errors psql client 'Cannot read termcap database; using dumb terminal settings' errors 'Console code page (...) differs from Windows code page (...)' error 'role "reader" already exists' error 'Ident authentication failed for user "postgres"' error 'Class Not Found: JDBC driver org.postgresql.Driver could not be loaded' error PostgreSQL server fails to start or restart For further reference Other issues not listed here Troubleshooting installation problemsIf things don't look like