Re: I have no idea why pg_dump isn't dumping all of my data

PostgreSQL: Documentation: 13: 37.17. Packaging Related Objects into an
Extension <https://www.postgresql.org/docs/13/extend-extensions.html>

so it works as expected.
someone would have to point to the reference wrt modification of data in
objects created via extension.

The main advantage of using an extension, rather than just running the
SQL script
to load a bunch of “loose” objects into your database, is that PostgreSQL will
then understand that the objects of the extension go together. You can drop
all the objects with a single DROP EXTENSION
<https://www.postgresql.org/docs/13/sql-dropextension.html> command (no
need to maintain a separate “uninstall” script). Even more useful,
pg_dump knows
that it should not dump the individual member objects of the extension — it
will just include a CREATE EXTENSION command in dumps, instead. This vastly
simplifies migration to a new version of the extension that might contain
more or different objects than the old version. Note however that you must
have the extension's control, script, and other files available when
loading such a dump into a new database.

On Fri, 21 May 2021 at 17:07, Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

> so this is the summary.
>
> I modified the extension to have a simple sql that created table and
> inserted a value.
>
> ***********************************************
>
> postgres(at)go:~$ cat
> /opt/postgresql-13/local/share/extension/nanoscopic--1.0.sql
>
>
> create table foo(id int);
> insert into foo values (1);
>
>
> postgres(at)go:~$ cat
> /opt/postgresql-13/local/share/extension/nanoscopic.control
> default_version = '1.0'
> comment = 'Database requirements for the Nanoscopic blogging platform'
> encoding = UTF8
> superuser = false
> trusted = false
>
> postgres(at)go:~$ psql foobar
> psql (13.2)
> Type "help" for help.
>
> foobar=# drop extension nanoscopic;
> DROP EXTENSION
> foobar=# \dt
> Did not find any relations.
> foobar=# \q
> postgres(at)go:~$ stoppg
> waiting for server to shut down.... done
> server stopped
> postgres(at)go:~$ startpg
> waiting for server to start.... done
> server started
> postgres(at)go:~$ psql foobar
> psql (13.2)
> Type "help" for help.
>
> foobar=# set role demo;
> SET
> foobar=> create extension nanoscopic;
> CREATE EXTENSION
> foobar=> \dt
> List of relations
> Schema | Name | Type | Owner
> --------+------+-------+-------
> public | foo | table | demo
> (1 row)
>
> foobar=> table foo;
> id
> ----
> 1
> (1 row)
>
> foobar=> insert into foo values (2); -- i add more data to the table
> created via extension
> INSERT 0 1
> foobar=> table foo;
> id
> ----
> 1
> 2
> (2 rows)
>
> foobar=> \q
> postgres(at)go:~$ pg_dump
> --file=nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql --dbname=foobar
> --clean --create --if-exists --username=demo -v --host=127.0.0.1 --port=5432
> Password:
> 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 "public.foo"
> pg_dump: flagging inherited columns in subtables
> pg_dump: reading indexes
> pg_dump: flagging indexes in partitioned tables
> 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 "public.foo"
> pg_dump: reading policies for table "public.foo"
> pg_dump: reading publications
> pg_dump: reading publication membership
> 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: dropping DATABASE foobar
> pg_dump: creating DATABASE "foobar"
> pg_dump: connecting to new database "foobar"
> pg_dump: creating EXTENSION "nanoscopic"
> pg_dump: creating COMMENT "EXTENSION nanoscopic"
> postgres(at)go:~$ more nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql
> --
> -- PostgreSQL database dump
> --
>
> -- Dumped from database version 13.2
> -- Dumped by pg_dump version 13.2
>
> -- Started on 2021-05-21 17:03:32 IST
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET idle_in_transaction_session_timeout = 0;
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
> SELECT pg_catalog.set_config('search_path', '', false);
> SET check_function_bodies = false;
> SET xmloption = content;
> SET client_min_messages = warning;
> SET row_security = off;
>
> DROP DATABASE IF EXISTS foobar;
> --
> -- TOC entry 2238 (class 1262 OID 26804)
> -- Name: foobar; Type: DATABASE; Schema: -; Owner: postgres
> --
>
> CREATE DATABASE foobar WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE
> = 'en_US.utf8';
>
>
> ALTER DATABASE foobar OWNER TO postgres;
>
> \connect foobar
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET idle_in_transaction_session_timeout = 0;
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
> SELECT pg_catalog.set_config('search_path', '', false);
> SET check_function_bodies = false;
> SET xmloption = content;
> SET client_min_messages = warning;
> SET row_security = off;
>
> --
> -- TOC entry 2 (class 3079 OID 26997)
> -- Name: nanoscopic; Type: EXTENSION; Schema: -; Owner: -
> --
>
> CREATE EXTENSION IF NOT EXISTS nanoscopic WITH SCHEMA public;
>
>
> --
> -- TOC entry 2239 (class 0 OID 0)
> -- Dependencies: 2
> -- Name: EXTENSION nanoscopic; Type: COMMENT; Schema: -; Owner:
> --
>
> COMMENT ON EXTENSION nanoscopic IS 'Database requirements for the
> Nanoscopic blogging platform';
>
>
> -- Completed on 2021-05-21 17:03:34 IST
>
> --
> -- PostgreSQL database dump complete
> --
>
>
> the dump only refers to creation of extension.
> so when you load the extension via restore, it would create the extension
> and create the table foo and load one value as in sql script.
>
> but the inserted value of 2 is lost.
>
> so this happens.
> I do not know which part of docs mention that.
>
> but FYI.
>
>
>
>
> On Fri, 21 May 2021 at 16:56, Vijaykumar Jain <
> vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>
>> ok,
>>
>> I think this is what it is.
>>
>> I copied the files to the extensions folder.
>>
>> ls /opt/postgresql-13/local/share/extension/nanoscopic*
>> /opt/postgresql-13/local/share/extension/nanoscopic--1.0.sql
>> /opt/postgresql-13/local/share/extension/nanoscopic.control
>>
>> and loaded the extensions.
>> the relations are created as a result of the extension.
>>
>> foobar=# create extension nanoscopic;
>> CREATE EXTENSION
>> foobar=# \dt
>> List of relations
>> Schema | Name | Type | Owner
>> --------+-----------------------+-------+----------
>> public | blog | table | postgres
>> public | blog_page | table | postgres
>> public | blog_post | table | postgres
>> public | blog_post_comment | table | postgres
>> public | blog_user | table | postgres
>> public | blog_user_permissions | table | postgres
>> (6 rows)
>>
>> foobar=# drop extension nanoscopic;
>> DROP EXTENSION
>> foobar=# \dt
>> Did not find any relations.
>>
>>
>> when you dump the db, only the create extension statement is dumped, not
>> its relations.
>>
>> when you reload the db from the dump file, the extension is created and
>> relations too are created via that extension.
>>
>> But I do not know the theory of how pg_dump deals with relations and the
>> data created via extensions at load time and further when they are modified.
>>
>> I'll do some lookup on this.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, 21 May 2021 at 16:29, Simon Connah <simon(dot)n(dot)connah(at)protonmail(dot)com>
>> wrote:
>>
>>> This is the source code of the extension in question:
>>> https://github.com/xmrsoftware/nanoscopic/tree/master/sql/nanoscopic
>>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>>> On Friday, May 21st, 2021 at 11:29, Vijaykumar Jain <
>>> vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>>>
>>> i just did a dump of a db which was owned by postgres but some tables
>>> owned by other users and it ran fine.
>>> I am not sure of that nanoscopic extension though.
>>>
>>>
>>>
>>> *******************************
>>> createdb -e foobar;
>>>
>>> postgres=# \c foobar
>>> You are now connected to database "foobar" as user "postgres".
>>> foobar=# set role demo;
>>> SET
>>> foobar=> create table xx(id int);
>>> CREATE TABLE
>>> foobar=> \dt
>>> List of relations
>>> Schema | Name | Type | Owner
>>> --------+------+-------+-------
>>> public | xx | table | demo
>>> (1 row)
>>>
>>> foobar=> insert into xx values (1);
>>> INSERT 0 1
>>> foobar=> \dt
>>> List of relations
>>> Schema | Name | Type | Owner
>>> --------+------+-------+-------
>>> public | xx | table | demo
>>> (1 row)
>>>
>>> foobar=> \l
>>> List of databases
>>> Name | Owner | Encoding | Collate | Ctype | Access
>>> privileges
>>>
>>> -----------+----------+----------+------------+------------+-----------------------
>>> demo | demo_rw | UTF8 | en_US.utf8 | en_US.utf8 |
>>> foobar | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
>>> postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
>>> template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
>>> =c/postgres +
>>> | | | | |
>>> postgres=CTc/postgres
>>> template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
>>> =c/postgres +
>>> | | | | |
>>> postgres=CTc/postgres
>>> (5 rows)
>>> *******************************
>>>
>>>
>>> *******************************
>>> pg_dump --file=nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql
>>> --dbname=foobar --clean --create --if-exists --username=demo -v
>>> --host=127.0.0.1 --port=5432
>>>
>>> ... last lines from the verbose dump
>>>
>>> pg_dump: dropping DATABASE foobar
>>> pg_dump: creating DATABASE "foobar"
>>> pg_dump: connecting to new database "foobar"
>>> pg_dump: creating TABLE "public.xx"
>>> pg_dump: processing data for table "public.xx"
>>> pg_dump: dumping contents of table "public.xx"
>>>
>>>
>>> CREATE DATABASE foobar WITH TEMPLATE = template0 ENCODING = 'UTF8'
>>> LOCALE = 'en_US.utf8';
>>>
>>>
>>> ALTER DATABASE foobar OWNER TO postgres;
>>>
>>> \connect foobar
>>>
>>> SET statement_timeout = 0;
>>> SET lock_timeout = 0;
>>> SET idle_in_transaction_session_timeout = 0;
>>> SET client_encoding = 'UTF8';
>>> SET standard_conforming_strings = on;
>>> SELECT pg_catalog.set_config('search_path', '', false);
>>> SET check_function_bodies = false;
>>> SET xmloption = content;
>>> SET client_min_messages = warning;
>>> SET row_security = off;
>>>
>>> SET default_tablespace = '';
>>>
>>> SET default_table_access_method = heap;
>>>
>>> --
>>> -- TOC entry 200 (class 1259 OID 26105)
>>> -- Name: xx; Type: TABLE; Schema: public; Owner: demo
>>> --
>>>
>>> CREATE TABLE public.xx (
>>> id integer
>>> );
>>>
>>>
>>> ALTER TABLE public.xx OWNER TO demo;
>>>
>>> --
>>> -- TOC entry 2232 (class 0 OID 26105)
>>> -- Dependencies: 200
>>> -- Data for Name: xx; Type: TABLE DATA; Schema: public; Owner: demo
>>> --
>>>
>>> COPY public.xx (id) FROM stdin;
>>> 1
>>> \.
>>>
>>>
>>> -- Completed on 2021-05-21 15:54:08 IST
>>>
>>> --
>>> -- PostgreSQL database dump complete
>>> --
>>> *******************************
>>> works fine.
>>> I do not know that extension(nanoscopic) though.
>>>
>>> it is reading some tables in a public schema, but not even dumping the
>>> schema.
>>>
>>> yep, thats odd if it does not throw any errors, coz any errors wrt
>>> permissions are thrown right away to console.
>>>
>>> maybe someone with more exp would be able to help.
>>>
>>>
>>> On Fri, 21 May 2021 at 15:32, Simon Connah <
>>> simon(dot)n(dot)connah(at)protonmail(dot)com> wrote:
>>>
>>>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>>>> On Friday, May 21st, 2021 at 10:55, Vijaykumar Jain <
>>>> vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>>>>
>>>> Can you try dumping using verbose flag.
>>>> -v
>>>>
>>>> Just want to confirm if the user has relevant permissions.
>>>>
>>>>
>>>> On Fri, May 21, 2021, 3:04 PM Simon Connah <
>>>> simon(dot)n(dot)connah(at)protonmail(dot)com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I'm running the following command to dump my database:
>>>>>
>>>>> /usr/bin/pg_dump
>>>>> --file=/home/simon/nanoscopic_db_127_0_0_1-2021_05_21_10_25_28-dump.sql
>>>>> --dbname=nanoscopic_db --clean --create --if-exists
>>>>> --username=nanoscopic_db_user --host=127.0.0.1 --port=5432
>>>>>
>>>>> and yet when I run that all I get in the SQL file is the following:
>>>>>
>>>>> https://gist.github.com/simonconnah/e1a15b1536b6e519b84481ae74f082bf
>>>>>
>>>>> I'm at a total loss. I've tried all the relevant looking command line
>>>>> switches and nothing seems to dump the actual contents of the database. It
>>>>> just dumps the extension command. Can anyone help me to figure this out
>>>>> please? It is probably something stupid that I am doing wrong.
>>>>>
>>>>> Simon.
>>>>>
>>>>
>>>> *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 "public.blog_user"
>>>> *pg_dump: *finding default expressions of table "public.blog_user"
>>>> *pg_dump: *finding check constraints for table "public.blog_user"
>>>> *pg_dump: *finding the columns and types of table "public.blog"
>>>> *pg_dump: *finding default expressions of table "public.blog"
>>>> *pg_dump: *finding the columns and types of table "public.blog_post"
>>>> *pg_dump: *finding default expressions of table "public.blog_post"
>>>> *pg_dump: *finding check constraints for table "public.blog_post"
>>>> *pg_dump: *finding the columns and types of table
>>>> "public.blog_post_comment"
>>>> *pg_dump: *finding default expressions of table
>>>> "public.blog_post_comment"
>>>> *pg_dump: *finding the columns and types of table "public.blog_page"
>>>> *pg_dump: *finding default expressions of table "public.blog_page"
>>>> *pg_dump: *finding the columns and types of table
>>>> "public.blog_user_permissions"
>>>> *pg_dump: *finding default expressions of table
>>>> "public.blog_user_permissions"
>>>> *pg_dump: *flagging inherited columns in subtables
>>>> *pg_dump: *reading indexes
>>>> *pg_dump: *reading indexes for table "public.blog_user"
>>>> *pg_dump: *reading indexes for table "public.blog"
>>>> *pg_dump: *reading indexes for table "public.blog_post"
>>>> *pg_dump: *reading indexes for table "public.blog_post_comment"
>>>> *pg_dump: *reading indexes for table "public.blog_page"
>>>> *pg_dump: *reading indexes for table "public.blog_user_permissions"
>>>> *pg_dump: *flagging indexes in partitioned tables
>>>> *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
>>>> "public.blog_user_blog_user_id_seq"
>>>> *pg_dump: *reading policies for table
>>>> "public.blog_user_blog_user_id_seq"
>>>> *pg_dump: *reading row security enabled for table "public.blog_user"
>>>> *pg_dump: *reading policies for table "public.blog_user"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_blog_id_seq"
>>>> *pg_dump: *reading policies for table "public.blog_blog_id_seq"
>>>> *pg_dump: *reading row security enabled for table "public.blog"
>>>> *pg_dump: *reading policies for table "public.blog"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_post_blog_post_id_seq"
>>>> *pg_dump: *reading policies for table
>>>> "public.blog_post_blog_post_id_seq"
>>>> *pg_dump: *reading row security enabled for table "public.blog_post"
>>>> *pg_dump: *reading policies for table "public.blog_post"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_post_comment_blog_post_comment_id_seq"
>>>> *pg_dump: *reading policies for table
>>>> "public.blog_post_comment_blog_post_comment_id_seq"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_post_comment"
>>>> *pg_dump: *reading policies for table "public.blog_post_comment"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_page_blog_page_id_seq"
>>>> *pg_dump: *reading policies for table
>>>> "public.blog_page_blog_page_id_seq"
>>>> *pg_dump: *reading row security enabled for table "public.blog_page"
>>>> *pg_dump: *reading policies for table "public.blog_page"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_user_permissions_blog_user_permissions_id_seq"
>>>> *pg_dump: *reading policies for table
>>>> "public.blog_user_permissions_blog_user_permissions_id_seq"
>>>> *pg_dump: *reading row security enabled for table
>>>> "public.blog_user_permissions"
>>>> *pg_dump: *reading policies for table "public.blog_user_permissions"
>>>> *pg_dump: *reading publications
>>>> *pg_dump: *reading publication membership
>>>> *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: *dropping DATABASE nanoscopic_db
>>>> *pg_dump: *creating DATABASE "nanoscopic_db"
>>>> *pg_dump: *connecting to new database "nanoscopic_db"
>>>> *pg_dump: *creating EXTENSION "nanoscopic"
>>>> *pg_dump: *creating COMMENT "EXTENSION nanoscopic"
>>>> *pg_dump: *creating ACL "DATABASE nanoscopic_db"
>>>>
>>>
>>>
>>> --
>>> Thanks,
>>> Vijay
>>> Mumbai, India
>>>
>>>
>>
>> --
>> Thanks,
>> Vijay
>> Mumbai, India
>>
>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>

--
Thanks,
Vijay
Mumbai, India