PostgreSQL script

Question

PostgreSQL script

Velmurugan 1

@AnuragSharma-MSFT, As discussed I'm starting a new thread inorder to get the PostgreSQL script.

The script should have the details to collect all the data information such as object count, row count, parameter values, namespace, constraints etc. In other words, the script should collect all the information about the Souce postgreSQL database. This will be helpful in order to validate everything once the Migration is completed.

Thanks,
Velmurugan

Anurag Sharma 17,631 Reputation points

2022-07-04T05:41:45.253+00:00
Hi @Velmurugan , thanks for reaching out to us.

For getting the count of all PostgreSQL database object we can use below query:

select n.nspname as schema_name ,c.relname as object_name ,r.rolname as object_owner ,case c.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW' when 'i' then 'INDEX' when 'S' then 'SEQUENCE' when 'v' then 'VIEW' when 'c' then 'TYPE' else c.relkind::text end as object_type from pg_class c join pg_roles r on r.oid = c.relowner join pg_namespace n on n.oid = c.relnamespace where n.nspname not in ('information_schema', 'pg_catalog') and n.nspname not like 'pg_toast%' order by n.nspname, c.relname;

This will return all the objects, however we can do group by to get the count as well.

PG query to count all objects

For getting the tables row count, you can use any suggestion from the below thread:

How do you find the row count for all your tables in Postgres

However, if you use Azure Database Migration Service to migrate the databases from an on-premises PostgreSQL instance, this can be verified using the status output as shown in below screenshot:

Tutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online using DMS via the Azure CLI

Please let us know if this helps or else, we can discuss further on this.
Velmurugan 1 Reputation point

2022-07-04T16:46:37.433+00:00

@AnuragSharma-MSFT, Not all my questions are answered

I have to check the object status in postgresql ( whether valid or invalid) query to check that?
How to set the page size, line size and col in Postgresql ?

Regards,
Velmurugan
Anurag Sharma 17,631 Reputation points

2022-07-07T06:16:20.587+00:00

Apologies for delayed response.

We can check the valid/invalid objects using below query. This will show all the invalid indexes which are only objects that could become invalid. Let me know if you think otherwise.

SELECT pg_class.relname FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

How to show all invalid objects in PostgresQL

For block size, I dont think we can set it up in Azure PostgreSQL as it is paas service, by default it is configured with 8192 bytes, let me see with product group as well.
Velmurugan 1 Reputation point

2022-07-07T10:27:55.007+00:00

@AnuragSharma-MSFT, Firstly to clarify I didn't ask anything related to block size.

Basically, I wanted to get the valid and invalid objects details in the PostgreSQL database and it is not specific to index. I need to check for all objects in the database.

Also, I wanted to format the columns and set the pages and lines in DB.

Regards,
Velmurugan

1 answer

Your answer

Anurag Sharma 17,631 Reputation points

2022-07-04T05:41:45.253+00:00

Hi @Velmurugan , thanks for reaching out to us.

For getting the count of all PostgreSQL database object we can use below query:

select n.nspname as schema_name ,c.relname as object_name ,r.rolname as object_owner ,case c.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW' when 'i' then 'INDEX' when 'S' then 'SEQUENCE' when 'v' then 'VIEW' when 'c' then 'TYPE' else c.relkind::text end as object_type from pg_class c join pg_roles r on r.oid = c.relowner join pg_namespace n on n.oid = c.relnamespace where n.nspname not in ('information_schema', 'pg_catalog') and n.nspname not like 'pg_toast%' order by n.nspname, c.relname;

This will return all the objects, however we can do group by to get the count as well.

PG query to count all objects

For getting the tables row count, you can use any suggestion from the below thread:

How do you find the row count for all your tables in Postgres

However, if you use Azure Database Migration Service to migrate the databases from an on-premises PostgreSQL instance, this can be verified using the status output as shown in below screenshot:

Tutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online using DMS via the Azure CLI

Please let us know if this helps or else, we can discuss further on this.
Velmurugan 1 Reputation point

2022-07-04T16:46:37.433+00:00

@AnuragSharma-MSFT, Not all my questions are answered

I have to check the object status in postgresql ( whether valid or invalid) query to check that?
How to set the page size, line size and col in Postgresql ?

Regards,
Velmurugan
Anurag Sharma 17,631 Reputation points

2022-07-07T06:16:20.587+00:00

Apologies for delayed response.

We can check the valid/invalid objects using below query. This will show all the invalid indexes which are only objects that could become invalid. Let me know if you think otherwise.

SELECT pg_class.relname FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

How to show all invalid objects in PostgresQL

For block size, I dont think we can set it up in Azure PostgreSQL as it is paas service, by default it is configured with 8192 bytes, let me see with product group as well.
Velmurugan 1 Reputation point

2022-07-07T10:27:55.007+00:00

@AnuragSharma-MSFT, Firstly to clarify I didn't ask anything related to block size.

Basically, I wanted to get the valid and invalid objects details in the PostgreSQL database and it is not specific to index. I need to check for all objects in the database.

Also, I wanted to format the columns and set the pages and lines in DB.

Regards,
Velmurugan

Answer 1

si li 20

You can refer to system view information_schema.tables and system catalog pg_catalog.pg_class.

Write any SQL query as you want.

thanks.

Share via

PostgreSQL script

1 answer

Your answer