Error database postgres already exists

help ERROR: database already exists (pgsql://@/nominatim) #224 Comments good day to all I need help please I have installed map of brazil but when I am installing Argentina estae I get Error # ./Nominatim/utils/setup.php —osm-file argentina-latest.osm.pbf —all 2>&1 | tee import.txt Create DB ERROR: database already exists (pgsql://@/nominatim) database already exists (pgsql://@/nominatim) I need […]

Содержание

  1. help ERROR: database already exists (pgsql://@/nominatim) #224
  2. Comments
  3. Footer
  4. How to create and delete databases and tables in PostgreSQL
  5. CONTENT

help ERROR: database already exists (pgsql://@/nominatim) #224

good day to all
I need help please
I have installed map of brazil
but when I am installing Argentina estae I get Error

# ./Nominatim/utils/setup.php —osm-file argentina-latest.osm.pbf —all 2>&1 | tee import.txt
Create DB
ERROR: database already exists (pgsql://@/nominatim)
database already exists (pgsql://@/nominatim)

I need to install if or if urgent

The text was updated successfully, but these errors were encountered:

You cannot use setup.php to add more data to an existing database. For more information on how to do that, see https://help.openstreetmap.org/questions/15505/import-more-osm-files-in-to-nominatim

You have three possibilities:

  1. delete the existing database in postgres ( psql , then drop database nominatim ). Now you can only search Argentina because the Brazil data is no longer in the database
  2. change the database name in settings/local.php for example to nominatim-argentina . The PHP frontend uses the same configuration file. Now you can search Argentina. You can still search Brazil when you change the configuration file again. You can’t search both at the same time.
  3. merge the input files brazil-latest.osm.pbf and argentina-latest.osm.pbf . Delete the nominatim database and input the new file. In case of those two countries I would just import the complete South America file http://download.geofabrik.de/south-america.html

P.S. you shouldn’t run Nominatim as user root

# ./Nominatim/utils/update.php —import-file brazil-latest.osm.pbf
WARNING: resetting cache memory to 502
/usr/bin/osmosis —read-xml ‘/root/brazil-latest.osm.pbf’ —read-empty —derive-change —write-xml-change /root/Nominatim/data/osmosischange.osc
Jan 20, 2015 1:47:38 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.40.1
Jan 20, 2015 1:47:39 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jan 20, 2015 1:47:39 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Jan 20, 2015 1:47:39 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Jan 20, 2015 1:47:39 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 1-read-xml failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to parse xml file /root/brazil-latest.osm.pbf. publicId=(null), systemId=(null), lineNumber=1, columnNumber=1.
at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:113)
at java.lang.Thread.run(Thread.java:679)
Caused by: org.xml.sax.SAXParseException: Content is not allowed in prolog.
at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)
at org.apache.xerces.util.ErrorHandlerWrapper.fatalError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLScanner.reportFatalError(Unknown Source)
at org.apache.xerces.impl.XMLDocumentScannerImpl$PrologDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
at javax.xml.parsers.SAXParser.parse(SAXParser.java:189)
at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:108)
. 1 more
Jan 20, 2015 1:47:39 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion
SEVERE: Thread for task 3-derive-change failed
org.openstreetmap.osmosis.core.OsmosisRuntimeException: An input error has occurred, aborting.
at org.openstreetmap.osmosis.core.store.DataPostbox.checkForInputErrors(DataPostbox.java:90)
at org.openstreetmap.osmosis.core.store.DataPostbox.consumeCentralQueue(DataPostbox.java:160)
at org.openstreetmap.osmosis.core.store.DataPostbox.hasNext(DataPostbox.java:237)
at org.openstreetmap.osmosis.set.v0_6.ChangeDeriver.run(ChangeDeriver.java:158)
at java.lang.Thread.run(Thread.java:679)
Jan 20, 2015 1:47:39 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed.
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:328)
at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238)
at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:408)
at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:351)
at org.codehaus.classworlds.Launcher.main(Launcher.java:31)
Error converting osm to osc, osmosis returned: 1

The page lonvia posted also has the answer to that.
You need to execute —read-pbf instead of —read-xml . You might need to edit update.php for this to work.

How to merge two or more pbf files?

$ sudo apt-get install osmctools
$ mkfifo p1 p2
$ osmconvert a.pbf —out-o5m -o=p1 & osmconvert b.pbf —out-o5m -o=p2 & osmconvert p1 p2 -o=all.pbf

or
$osmosis —read-pbf file=zone.pbf —read-pbf file=modif.pbf —merge —write-pbf omitmetadata=true file=zone-modif.pbf

May I suggest to ask the question on the https://help.openstreetmap.org/ forum?

This issue is from another user, from January, has a different topic and is closed.

I know, but google related to this issue for «merge pbf nominatim», may be it will helped somebody.

I accidentally cancelled the process. I do not want to run the whole process again. It took almost a day. Is there a way around it ? I am trying to import Canada’s map

@tanzeelrana Please open a new issue. This one got closed 18 months ago. It will help if you can you tell where the processed got stopped (last 10 lines of the output for example).

© 2023 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

How to create and delete databases and tables in PostgreSQL

CONTENT

PostgreSQL and other relational database management systems use databases and tables to structure and organize their data. We can review the definition of those two terms quickly:

  • databases: separate different sets of structures and data from one another
  • tables: define the data structure and store the actual data values within databases

In PostgreSQL, there is also an intermediary object between databases and tables called schema:

  • schema: a namespace within a database that contains tables, indexes, views, and other items.

This guide won’t deal directly with PostgreSQL’s concept of a schema, but it’s good to know it’s there.

Instead, we’ll be focusing on how to create and destroy PostgreSQL databases and tables. The examples will primarily use SQL, but towards the end, we’ll show you how to do a few of these tasks using the command line. These alternatives use tools included in the standard PostgreSQL installation that are available if you have administrative access to the PostgreSQL host.

Some of the statements covered in this guide, particularly the PostgreSQL CREATE TABLE statement, have many additional options that were outside of the scope of this article. If you’d like additional information, find out more by checking out the official PostgreSQL documentation.

To follow along with this guide, you will need to log in to a PostgreSQL instance with a user with administrative privileges using the psql command line client. Your PostgreSQL instance can be installed locally, remotely, or provisioned by a provider.

Specifically, your PostgreSQL user will need the CREATE DB privilege or be a Superuser , which you can check with the du meta-command in psql :

The postgres superuser, which is created automatically upon installation, has the required privileges, but you can use any user with the Create DB privilege.

Create a new database

Once you are connected to your PostgreSQL instance using psql or any other SQL client, you can create a database using SQL.

The basic syntax for creating a database is:

This will create a database called db_name on the current server with the current user set as the new database’s owner using the default database settings. You can view the properties of the default template1 template using the following psql meta-command:

You can add additional parameters to alter the way your database is created. These are some common options:

  • ENCODING: sets the character encoding for the database.
  • LC_COLLATE: sets the collation, or sort, order for the database. This is a localization option that determines how items are organized when they are ordered.
  • LC_CTYPE: sets the character classification for the new database. This is a localization option that affects what characters are considered uppercase, lowercase, and digits.

These can help ensure that the database can store data in the formats you plan to support and with your project’s localization preferences.

For example, to ensure that your database is created with Unicode support and to override the server’s own locale to use American English localization (these all happen to match the values in the template1 shown above, so no change will actually occur), you could type:

To follow along with the examples in this guide, create a database called school using your instance’s default locale settings and the UTF8 character encoding:

This will create your new database using the specifications you provided.

List existing databases

To determine what databases are currently available on your server or cluster, you can use the following SQL statement:

This will list each of the databases currently defined within the environment:

As mentioned before, if you are connected using the psql client, you can also get this information l meta-command:

This will show the available database names along with their owners, encoding, locale settings, and privileges:

The school database that we created is displayed among the other databases on the system. This is a good way to get an overview of the databases within your server or cluster.

Create tables within databases

After creating one or more databases, you can begin to define tables to store your data. Tables consist of a name and a defined schema which determines the fields and data types that each record must contain.

PostgreSQL CREATE TABLE syntax

You can create tables using the CREATE TABLE statement. A simplified basic syntax for the command looks like the following:

The components of the above syntax include the following:

  • CREATE TABLE table_name : The basic creation statement that signals that you wish to define a table. The table_name placeholder should be replaced with the name of the table you wish to use.
  • column_name TYPE : Defines a basic column within the table. The column_name placeholder should be replaced with the name you wish to use for your column. The TYPE specifies the PostgreSQL data type for the column. Data stored within the table must conform to the column structure and column data types to be accepted.
  • column_constraint : Column constraints are optional restraints to add further restrictions on the data that can be stored in the column. For example, you can require that entries be not null, unique, or positive integers.
  • table_constraints : Table constraints are similar to column constraints but involve the interaction of multiple columns. For instance, you could have a table constraint that checks that a DATE_OF_BIRTH is before DATE_OF_DEATH in a table.

Create tables conditionally with the IF NOT EXISTS clause

By default, if you attempt to create a table in PostgreSQL that already exists within the database, an error will occur. To work around this problem in cases where you want to create a table if it isn’t present, but just continue on if it already exists, you can use the IF NOT EXISTS clause. The IF NOT EXISTS optional qualifier that tells PostgreSQL to ignore the statement if the database already exists.

To use the IF NOT EXISTS clause, insert it into the command after the CREATE TABLE syntax and before the table name:

This variant will attempt to create the table. If a table with that name already exists within the specified database, PostgreSQL will throw a warning indicating that the table name was already taken instead of failing with an error.

How to create tables in PostgreSQL

The above syntax is enough to create basic tables. As an example, we’ll create two tables within our school database. One table will be called supplies and the other will be called teachers :

In the supplies table, we want to have the following fields:

  • ID: A unique ID for each type of school supply.
  • Name: The name of a specific school item.
  • Description: A short description of the item.
  • Manufacturer: The name of the item manufacturer.
  • Color: The color of the item.
  • Inventory: The number of items we have for a certain type of school supply. This should never be less than 0.

We can create the supplies table with the above qualities using the following SQL.

First, change to the school database you created with psql by typing:

This will change the database that our future commands will target. Your prompt should change to reflect the database.

Next, create the supplies table with the following statement:

This will create the supplies table within the school database. The PRIMARY KEY column constraint is a special constraint used to indicate columns that can uniquely identify records within the table. As such, the constraint specifies that the column cannot be null and must be unique. PostgreSQL creates indexes for primary key columns to increase querying speed.

Verify that the new table is present by typing:

Verify that the schema reflects the intended design by typing:

We can see each of the columns and data types that we specified. The column constraint that we defined for the inventory column is listed towards the end.

Next, we will create a teachers table. In this table, the following columns should be present:

  • Employee ID: A unique employee identification number.
  • First name: The teacher’s first name.
  • Last name: The teacher’s last name.
  • Subject: The subject that the teacher is hired to teach.
  • Grade level: The grade level of students that the teach is hired to teach.

Create the teachers table with the above schema with the following SQL:

How to create tables with primary keys and foreign keys

You can find information about creating tables with primary and foreign keys in some of our other PostgreSQL guides. Primary keys and foreign keys are both types of database constraint within PostgreSQL.

A primary key is a special column or column that is guaranteed to be unique across rows within the same table. All primary keys can be used to uniquely identify a specific row. Primary keys not only ensure that each row has a unique value for the primary key columns, they also ensure that no rows contain NULL values for that column. Often, the primary key in PostgreSQL uses the following format to specify an automatically assigned incrementing primary key: id SERIAL PRIMARY KEY .

Foreign keys are a way to ensure that a column or columns in one table match the values contained within another table. This helps ensure referential integrity between tables.

How to view tables in PostgreSQL

In PostgreSQL you can list tables in a few different ways depending on what information you are looking for.

If you’d like to see what tables are available within your database, you can use the dt meta-command included with the psql client to list all tables, as we demonstrated above:

You can also check that the schema for the table matches your specifications:

The teachers table seems to match our definition.

If you need to change the schema of an existing table in PostgreSQL, you can use the ALTER TABLE command. The ALTER TABLE command is very similar to the CREATE TABLE command, but operates on an existing table.

Alter table syntax

The basic syntax for modifying tables in PostgreSQL looks like this:

The indicates the exact type of change you would like to make, whether it involves setting different options on the table, adding or removing columns, or changing types or constraints. The part of the command contains any additional information that PostgreSQL needs to complete the change.

Adding columns to tables

You can add a column to a PostgreSQL table with the ADD COLUMN change command. The change parameters will include the column name, type, and options, just as you would specify them in the CREATE TABLE command.

For example, to add a column called missing_column of the text type to a table called some_table , you would type:

Removing columns from tables

If, instead, you’d like to remove an existing column, you can use the DROP COLUMN command instead. You need to specify the name of the column you wish to drop as a change parameter:

Changing the data type of a column

To change the data type that PostgreSQL uses for a specific column, you can use ALTER COLUMN change command with the SET DATA TYPE column command. The parameters include the column name, its new type, and an optional USING clause to specify how the old type should be converted to the new type.

For example, to set the value of a id column in the resident table to a int using an explicit cast, we can type the following:

Other table changes

Many other types of changes can be achieved with the ALTER TABLE command. For more information about the options available, check out the official PostgreSQL documentation for ALTER TABLE .

If you wish to delete a table, you can use the DROP TABLE SQL statement. This will delete the table as well as any data stored within it.

The basic syntax looks like this:

This will delete the table if it exists and throw an error if the table name does not exist.

If you wish to delete the table if it exists and do nothing if it does not exist, you can include the IF EXISTS qualifier within the statement:

Tables that have dependencies on other tables or objects cannot be deleted by default while those dependencies exist. To avoid the error, you can optionally include the CASCADE parameter, which automatically drops any dependencies along with the table:

If any tables have a foreign key constraint, which references the table that you are deleting, that constraint will automatically be deleted.

Delete the supplies table we created earlier by typing:

We will keep the teachers database to demonstrate that the statement to delete databases also removes all child objects like tables.

The DROP DATABASE statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

Replace the database_name placeholder with the name of the database you wish to remove. This will delete the database if it is found. If the database cannot be found, an error will occur:

If you wish to delete the database if it exists and otherwise do nothing, include the optional IF EXISTS option:

This will remove the database or do nothing if it cannot be found.

To remove the school database that we used in this guide, list the existing databases on your system:

Open a new connection to one of the databases you do not wish to delete:

Once the new connection is open, delete the school database with the following command:

This will remove the school database along with the teachers table defined within.

If you have been following along using SQL, you can end here or skip to the conclusion. If you’d like to learn about how to create and delete databases from the command line, continue on to the next section.

Using administrative command line tools to create and delete databases

If you have shell access to the server or cluster where PostgreSQL is installed, you may have access to some additional command line tools that can help create and delete databases. The createdb and dropdb commands are bundled with PostgreSQL when it is installed.

Create a new database from the command line

The basic syntax for the createdb command (which should be run by a system user with admin access to PostgreSQL) is:

This will create a database called db_name within PostgreSQL using the default settings.

The command also accepts options to alter its behavior, much like the SQL variant you saw earlier. You can find out more about these options with man createdb . Some of the most important options are:

  • —encoding= : sets the character encoding for the database.
  • —locale= : sets the locale for the database.

These can help ensure that the database can store data in the formats you plan to support and with your project’s localization preferences.

For example, to ensure that your database is created with Unicode support and to override the server’s own locale to use American English localization, you could type:

Assuming you have the correct permissions, the database will be created according to your specifications.

To follow along with the examples in this guide, you could create a database called school using the default locale and the UTF8 character encoding by typing:

You could then connect to the database using psql to set up your tables as usual.

Drop databases from the command line

The dropdb command mirrors the DROP DATABASE SQL statement. It has the following basic syntax:

Change the database_name placeholder to reference the database you wish to delete.

By default, this command will result in an error if the database specified cannot be found. To avoid this, you can include the optional —if-exists flag:

This will delete the specified database if it exists. Otherwise, it will do nothing.

To delete the school database we created earlier, type:

This will remove the database and any child elements, like tables, within.

This article covered the basics of how to create and delete databases and tables within PostgreSQL. These are some of the most basic commands required to set up a database system and being defining the structure of your data.

As mentioned earlier, the SQL statements covered in this PostgreSQL tutorial, particularly the CREATE TABLE statement, have many additional parameters can be used to change PostgreSQL’s behavior. You can find out more about these by checking out the official PostgreSQL documentation.

When using Prisma to develop with PostgreSQL, you will usually create databases and tables with Prisma Migrate. You can learn how use it in our guide on developing with Prisma Migrate.

Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.

Does PostgreSQL support the `IF NOT EXISTS` clause when using the `CREATE DATABASE` command?

Yes, PostgreSQL supports the use of IF NOT EXISTS when creating both databases and tables. The below demonstrates using the clause for table creation.

How do you create a database from a dump in PostgreSQL?

To create a database from a dump (pg_dump), PostgreSQL provides the utility program pg_restore .

This program recreates the database in the same state as it was at the time of the dump. Example syntax would look like the following:

Which command line creates a database in PostgreSQL?

To create a database in PostgreSQL, use the createdb command. The syntax is as follows:

How do you drop a database in PostgreSQL?

The DROP DATABASE statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

How do you change a column’s data type in PosgreSQL?

To change the data type for a specific column, use the ALTER COLUMN change command with the SET DATA TYPE column command.

The basic syntax includes column name, the new type, and an optional USING clause to specify the old type’s conversion.

Источник

Источник

In part one, Migrating PostgreSQL Databases From On-Prem to the Cloud Using AWS RDS, we showed you how to perform the migration. In this blog, we will explore some common errors that might pop up during the migration.

BACKUP ERROR: Permission Denied to Backup Schema

/usr/pgsql-10/bin/pg_dump -v source_database -h onpremdbserver.domain.com -p 5432 -U source_appuser -Fd -j 100 -f /dbbackup/10/source_database   --no-owner --no-privileges
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema source_schema
pg_dump: [archiver (db)] query was: LOCK TABLE source_schema.table1 IN ACCESS SHARE MODE

RESOLUTION – Ensure source_appuser has the proper access. I.e. in this case, source_appuser is owner

postgres=> c source_database
psql (10.5, server 10.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "source_database" as user "postgres".
source_database=> dn+
                               List of schemas
     Name      |   Owner   |   Access privileges    |      Description       
---------------+-----------+------------------------+------------------------
 public        | postgres | postgres=UC/postgres+| standard public schema
               |           | =UC/postgres          |
 source_schema | postgres |                        |
(2 rows)
 
source_database=> alter schema source_schema owner to source_appuser ;
ALTER SCHEMA
 
source_database=> dt source_schema.table*
             List of relations
    Schema     |  Name  | Type  |   Owner  
---------------+--------+-------+-----------
 source_schema | table1 | table | postgres
 source_schema | table2 | table | postgres
 source_schema | table3 | table | postgres
(3 rows)
 
source_database=> alter table source_schema.table1 owner to source_appuser ;
ALTER TABLE
source_database=> alter table source_schema.table2 owner to source_appuser ;
ALTER TABLE
source_database=> alter table source_schema.table3 owner to source_appuser ;
ALTER TABLE
source_database=> dt source_schema.table*
                List of relations
    Schema     |  Name  | Type  |     Owner     
---------------+--------+-------+----------------
 source_schema | table1 | table | source_appuser
 source_schema | table2 | table | source_appuser
 source_schema | table3 | table | source_appuser
(3 rows)

BACKUP ERROR: Server Version Mismatch

# Backup database...
/usr/pgsql-10/bin/pg_dump -v source_database -h onpremdbserver.domain.com -p 5432 -U source_appuser -Fd -j 100 -f /dbbackup/10/source_database   --no-owner --no-privileges
pg_dump: server version: 10.6; pg_dump version: 9.6.9
pg_dump: aborting because of server version mismatch

RESOLUTION – Use correct PostgreSQL binary (i.e. /usr/pgsql-10/bin Instead of /usr/pgsql-9.6/bin)

# Backup database...
/usr/pgsql-10/bin/pg_dump -v source_database -h onpremdbserver.domain.com -p 5432 -U source_appuser -Fd -j 100 -f /dbbackup/10/source_database   --no-owner --no-privileges
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "source_schema.table1"
pg_dump: finding the columns and types of table "source_schema.table2"
pg_dump: finding the columns and types of table "source_schema.table3"
pg_dump: finding default expressions of table "source_schema.table3"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "source_schema.table1"
pg_dump: reading policies for table "source_schema.table1"
pg_dump: reading row security enabled for table "source_schema.table2"
pg_dump: reading policies for table "source_schema.table2"
pg_dump: reading row security enabled for table "source_schema.table3_id_seq"
pg_dump: reading policies for table "source_schema.table3_id_seq"
pg_dump: reading row security enabled for table "source_schema.table3"
pg_dump: reading policies for table "source_schema.table3"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "source_schema.table1"
pg_dump: reading publication membership for table "source_schema.table2"
pg_dump: reading publication membership for table "source_schema.table3"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "source_schema.table1"
pg_dump: finished item 3797 TABLE DATA table1
pg_dump: dumping contents of table "source_schema.table3"
pg_dump: finished item 3800 TABLE DATA table3
pg_dump: dumping contents of table "source_schema.table2"
pg_dump: finished item 3798 TABLE DATA table2

RESTORATION ERROR: Must Be the Owner of Extension plpgsql

# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database   --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 3806 DATABASE source_database
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3807 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: creating SCHEMA "source_schema"
pg_restore: processing item 1 EXTENSION plpgsql
pg_restore: creating EXTENSION "plpgsql"
pg_restore: processing item 3808 COMMENT EXTENSION plpgsql
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3808; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
 
ERROR:  permission denied to create database

RESOLUTION: RDS does not allow true super user, but the error can be ignored.

RESTORATION ERROR: Permission Denied to Drop Database

ERROR:
postgres=> drop database dest_database_newdb ;
ERROR:  must be owner of database dest_database_newdb

RESOLUTION

postgres=> grant dest_rds_superuser to postgres ;
GRANT ROLE
postgres=> drop database dest_database_newdb ;
DROP DATABASE

RESTORATION ERROR: Permission Denied to Alter Role

# Grant dest_rds_superuser createdb...
/usr/pgsql-10/bin/psql -E -e postgres -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -Udest_rds_superuser -c"alter role dest_rds_superuser createdb"
alter role dest_rds_superuser createdb
ERROR:  permission denied

RESOLUTION

postgres=> grant rds_superuser to dest_rds_superuser ;
GRANT ROLE
 
 # Grant dest_rds_superuser createdb...
 /usr/pgsql-10/bin/psql -E -e postgres -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -Udest_rds_superuser -c"alter role dest_rds_superuser createdb"
 alter role dest_rds_superuser createdb
 ALTER ROLE

RESTORATION ERROR: Permission Denied to Create Database

ERROR:  permission denied to create database

RESOLUTION

RESOLUTION: Roles must have createdb privilege otherwise to avoid to the following error:
postgres=> alter role dest_rds_superuser createdb ;

PostgreSQL Management & Automation with ClusterControl

Learn about what you need to know to deploy, monitor, manage and scale PostgreSQL

RESTORATION ERROR: Database Already Exists

# Create database...
/usr/pgsql-10/bin/psql -E -e postgres -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -Udest_rds_superuser -c"create database dest_database_newdb"
create database dest_database_newdb
ERROR:  database "dest_database_newdb" already exists
# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database   --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 3806 DATABASE source_database
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3807 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: creating SCHEMA "source_schema"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 2615 20233 SCHEMA source_schema source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "source_schema" already exists
    Command was: CREATE SCHEMA source_schema;
       pg_restore: processing item 1 EXTENSION plpgsql
       pg_restore: creating EXTENSION "plpgsql"
       pg_restore: processing item 3808 COMMENT EXTENSION plpgsql
       pg_restore: creating COMMENT "EXTENSION plpgsql"
       pg_restore: [archiver (db)] Error from TOC entry 3808; 0 0 COMMENT EXTENSION plpgsql
       pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
           Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
       pg_restore: processing item 197 TABLE table1
       pg_restore: creating TABLE "source_schema.table1"
       pg_restore: [archiver (db)] Error from TOC entry 197; 1259 20234 TABLE table1 source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table1" already exists
           Command was: CREATE TABLE source_schema.table1 (
           id integer
       );
       pg_restore: processing item 198 TABLE table2
       pg_restore: creating TABLE "source_schema.table2"
       pg_restore: [archiver (db)] Error from TOC entry 198; 1259 20237 TABLE table2 source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table2" already exists
           Command was: CREATE TABLE source_schema.table2 (
           id integer
       );
       pg_restore: processing item 200 TABLE table3
       pg_restore: creating TABLE "source_schema.table3"
       pg_restore: [archiver (db)] Error from TOC entry 200; 1259 20242 TABLE table3 source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3" already exists
           Command was: CREATE TABLE source_schema.table3 (
           id integer NOT NULL,
           name character varying
       );
       pg_restore: processing item 199 SEQUENCE table3_id_seq
       pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
       pg_restore: [archiver (db)] Error from TOC entry 199; 1259 20240 SEQUENCE table3_id_seq source_appuser
       pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3_id_seq" already exists
           Command was: CREATE SEQUENCE source_schema.table3_id_seq
           AS integer
           START WITH 1
           INCREMENT BY 1
           NO MINVALUE
           NO MAXVALUE
           CACHE 1;
       pg_restore: processing item 3809 SEQUENCE OWNED BY table3_id_seq
       pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
       pg_restore: processing item 3675 DEFAULT table3 id
       pg_restore: creating DEFAULT "source_schema.table3 id"
       pg_restore: entering main parallel loop
       pg_restore: launching item 3797 TABLE DATA table1
       pg_restore: launching item 3798 TABLE DATA table2
       pg_restore: launching item 3800 TABLE DATA table3
       pg_restore: launching item 3810 SEQUENCE SET table3_id_seq
       pg_restore: pg_restore: executing SEQUENCE SET table3_id_seq
       processing data for table "source_schema.table2"
       pg_restore: finished item 3798 TABLE DATA table2
       pg_restore: finished item 3810 SEQUENCE SET table3_id_seq
       pg_restore: processing data for table "source_schema.table3"
       pg_restore: processing data for table "source_schema.table1"
       pg_restore: finished item 3797 TABLE DATA table1
       pg_restore: finished item 3800 TABLE DATA table3
       pg_restore: finished main parallel loop
       WARNING: errors ignored on restore: 6

RESOLUTION – Ensure you manually remove existing database before executing the script.

# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database   --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 3806 DATABASE source_database
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: processing item 3807 COMMENT SCHEMA public
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: creating SCHEMA "source_schema"
pg_restore: processing item 1 EXTENSION plpgsql
pg_restore: creating EXTENSION "plpgsql"
pg_restore: processing item 3808 COMMENT EXTENSION plpgsql
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3808; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
    Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
 
 
 
pg_restore: processing item 197 TABLE table1
pg_restore: creating TABLE "source_schema.table1"
pg_restore: processing item 198 TABLE table2
pg_restore: creating TABLE "source_schema.table2"
pg_restore: processing item 200 TABLE table3
pg_restore: creating TABLE "source_schema.table3"
pg_restore: processing item 199 SEQUENCE table3_id_seq
pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
pg_restore: processing item 3809 SEQUENCE OWNED BY table3_id_seq
pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
pg_restore: processing item 3675 DEFAULT table3 id
pg_restore: creating DEFAULT "source_schema.table3 id"
pg_restore: entering main parallel loop
pg_restore: launching item 3797 TABLE DATA table1
pg_restore: launching item 3798 TABLE DATA table2
pg_restore: launching item 3800 TABLE DATA table3

RESTORATION ERROR: Schema Already Exists

# Create schema...
/usr/pgsql-10/bin/psql -E -e -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 dest_database_newdb -Udest_rds_superuser -c"create schema source_schema"
create schema source_schema
ERROR:  schema "source_schema" already exists
 
# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database --schema=source_schema  --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: processing item 197 TABLE table1
pg_restore: creating TABLE "source_schema.table1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 197; 1259 20234 TABLE table1 source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table1" already exists
    Command was: CREATE TABLE source_schema.table1 (
    id integer
);
 
 
 
pg_restore: processing item 198 TABLE table2
pg_restore: creating TABLE "source_schema.table2"
pg_restore: [archiver (db)] Error from TOC entry 198; 1259 20237 TABLE table2 source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table2" already exists
    Command was: CREATE TABLE source_schema.table2 (
    id integer
);
 
  
pg_restore: processing item 200 TABLE table3
pg_restore: creating TABLE "source_schema.table3"
pg_restore: [archiver (db)] Error from TOC entry 200; 1259 20242 TABLE table3 source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3" already exists
    Command was: CREATE TABLE source_schema.table3 (
    id integer NOT NULL,
    name character varying
);
 
pg_restore: processing item 199 SEQUENCE table3_id_seq
pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
pg_restore: [archiver (db)] Error from TOC entry 199; 1259 20240 SEQUENCE table3_id_seq source_appuser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table3_id_seq" already exists
    Command was: CREATE SEQUENCE source_schema.table3_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
 
 
pg_restore: processing item 3806 SEQUENCE OWNED BY table3_id_seq
pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
pg_restore: processing item 3675 DEFAULT table3 id
pg_restore: creating DEFAULT "source_schema.table3 id"
pg_restore: entering main parallel loop
pg_restore: launching item 3797 TABLE DATA table1
pg_restore: launching item 3798 TABLE DATA table2
pg_restore: launching item 3800 TABLE DATA table3
pg_restore: launching item 3807 SEQUENCE SET table3_id_seq
pg_restore: pg_restore: processing data for table "source_schema.table2"
processing data for table "source_schema.table1"
pg_restore: executing SEQUENCE SET table3_id_seq
pg_restore: finished item 3797 TABLE DATA table1
pg_restore: finished item 3798 TABLE DATA table2
pg_restore: finished item 3807 SEQUENCE SET table3_id_seq
pg_restore: processing data for table "source_schema.table3"
pg_restore: finished item 3800 TABLE DATA table3
pg_restore: finished main parallel loop
WARNING: errors ignored on restore: 4

RESOLUTION – Either drop or rename existing schema. I like renaming until all is validated:

postgres=> c dest_database_newdb
psql (10.5, server 10.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "dest_database_newdb" as user "postgres".
dest_database_newdb=> dn
          List of schemas
     Name      |       Owner        
---------------+--------------------
 public        | postgres
 source_schema | dest_rds_superuser
(2 rows)
 
dest_database_newdb=> alter schema source_schema rename to source_schema_old;
ALTER SCHEMA
dest_database_newdb=> dn
            List of schemas
       Name        |       Owner        
-------------------+--------------------
 public            | postgres
 source_schema_old | dest_rds_superuser
(2 rows)
 
 
# Restore database...
/usr/pgsql-10/bin/pg_restore -v -d dest_database_newdb -hdest_dbinstance.cluster-awsrdsguid.us-east-1.rds.amazonaws.com -p5432 -U dest_rds_superuser -j 120 -Fd /dbbackup/10/source_database --schema=source_schema  --no-owner --no-privileges
pg_restore: connecting to database for restore
pg_restore: processing item 3803 ENCODING ENCODING
pg_restore: processing item 3804 STDSTRINGS STDSTRINGS
pg_restore: processing item 3805 SEARCHPATH SEARCHPATH
pg_restore: processing item 6 SCHEMA source_schema
pg_restore: processing item 197 TABLE table1
pg_restore: creating TABLE "source_schema.table1"
pg_restore: processing item 198 TABLE table2
pg_restore: creating TABLE "source_schema.table2"
pg_restore: processing item 200 TABLE table3
pg_restore: creating TABLE "source_schema.table3"
pg_restore: processing item 199 SEQUENCE table3_id_seq
pg_restore: creating SEQUENCE "source_schema.table3_id_seq"
pg_restore: processing item 3806 SEQUENCE OWNED BY table3_id_seq
pg_restore: creating SEQUENCE OWNED BY "source_schema.table3_id_seq"
pg_restore: processing item 3675 DEFAULT table3 id
pg_restore: creating DEFAULT "source_schema.table3 id"
pg_restore: entering main parallel loop
pg_restore: launching item 3797 TABLE DATA table1
pg_restore: launching item 3798 TABLE DATA table2
pg_restore: launching item 3800 TABLE DATA table3
pg_restore: launching item 3807 SEQUENCE SET table3_id_seq
pg_restore: processing data for table "source_schema.table1"
pg_restore: processing data for table "source_schema.table2"
pg_restore: executing SEQUENCE SET table3_id_seq
pg_restore: finished item 3807 SEQUENCE SET table3_id_seq
pg_restore: processing data for table "source_schema.table3"
pg_restore: finished item 3797 TABLE DATA table1
pg_restore: finished item 3798 TABLE DATA table2
pg_restore: finished item 3800 TABLE DATA table3
pg_restore: finished main parallel loop

Subscribe to get our best and freshest content

Источник

Странная ошибка. Вся помощь приветствуется.

Вот моя база данных.yml

development:
  adapter: postgresql
  encoding: unicode
  database: app_development
  pool: 5
  username: username
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: app_test
  pool: 5
  username: username
  password:

production:
  adapter: postgresql
  encoding: unicode
  database: app_production
  pool: 5
  username: username
  password:

Когда я создаю базы данных вручную и пытаюсь их использовать, например rake db:test:preare Я получаю следующее сообщение об ошибке:

FATAL:  database "postgres" does not exist

Когда я пытаюсь удалить базы данных, я получаю следующие ошибки:

Couldn't drop app_development : #<PG::Error: FATAL:  database "postgres" does not exist
>
Couldn't drop app_test : #<PG::Error: FATAL:  database "postgres" does not exist
>
Couldn't drop app_production : #<PG::Error: FATAL:  database "postgres" does not exist

Если я попытаюсь создать базы данных с помощью rake db:create:all Я получаю следующие ошибки:

app_development already exists
app_test already exists
app_production already exists

Итак, похоже, что с моей базой данных.yml все в порядке. Но по какой-то причине он ищет базу данных с именем postgres, когда это не то, что находится в моей базе данных.yml.

Любая помощь оценивается.

EDIT:

Вот еще из следа грабли:

Не удалось создать базу данных для {«adapter»=>»postgresql», «encoding»=>»unicode», «database»=>»app_production», «pool»=>5, «username»=>»username», «пароль»=>ноль}

Источник

Понравилась статья? Поделить с друзьями:
  • Error database is mandatory on usermailbox
  • Error database driver unknown driver postgres forgotten import
  • Error database connection failed moodle
  • Error data not accepted from server
  • Error data files corrupt please reinstall application fs 2023